Missing or invalid option oracle ошибка

The error you’re getting appears to be the result of the fact that there is no underscore between «chartered» and «flight» in the table name. I assume you want something like this where the name of the table is chartered_flight.

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)

Generally, there is no benefit to declaring a column as CHAR(3) rather than VARCHAR2(3). Declaring a column as CHAR(3) doesn’t force there to be three characters of (useful) data. It just tells Oracle to space-pad data with fewer than three characters to three characters. That is unlikely to be helpful if someone inadvertently enters an incorrect code. Potentially, you could declare the column as VARCHAR2(3) and then add a CHECK constraint that LENGTH(takeoff_at) = 3.

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL CHECK( length( takeoff_at ) = 3 )
, destination CHAR (3) NOT NULL CHECK( length( destination ) = 3 )
)

Since both takeoff_at and destination are airport codes, you really ought to have a separate table of valid airport codes and define foreign key constraints between the chartered_flight table and this new airport_code table. That ensures that only valid airport codes are added and makes it much easier in the future if an airport code changes.

And from a naming convention standpoint, since both takeoff_at and destination are airport codes, I would suggest that the names be complementary and indicate that fact. Something like departure_airport_code and arrival_airport_code, for example, would be much more meaningful.

ORA-00922 means that SQL parser found an invalid option where should be empty or one of other valid options it expects.

Since it’s a broadly used error, the root cause could be very different from case to case.

ALTER USER

Case 1

Tried to grant some quota on tablespace to an user, but it failed with ORA-00922.

SQL> alter user hr quota 100g on tablespace users;
alter user hr quota 100g on tablespace users
                                       *
ERROR at line 1:
ORA-00922: missing or invalid option

In this case, the keyword TABLESPACE shall not be used in this clause, just use a normal tablespace name after ON.

SQL> alter user hr quota 100g on users;

Case 2

Tried to lock an user, but it failed with ORA-00922.

SQL> alter user hr lock;
alter user hr lock
              *
ERROR at line 1:
ORA-00922: missing or invalid option

In the above, we missed the keyword ACCOUNT before LOCK.

SQL> alter user hr account lock;

CREATE TABLE

Case 1

Tried to use CTAS to create a table to another tablespace, but it failed with ORA-00922.

SQL> create table t2 tablespace users select * from t1;
create table t2 tablespace users select * from t1
                                 *
ERROR at line 1:
ORA-00922: missing or invalid option

In the above, we missed the keyword AS before SELECT.

SQL> create table t2 tablespace users as select * from t1;

Case 2

Tried to use CTAS to create a table to another tablespace, but it failed with ORA-00922.

SQL> create table t2 users as select * from t1;
create table t2 users as select * from t1
                *
ERROR at line 1:
ORA-00922: missing or invalid option

In the above, we missed the keyword TABLESPACE before the tablespace name USERS.

SQL> create table t2 tablespace users as select * from t1;

Hi Friends,

This is a very tricky and suspenseful error called ORA-00922 with strange detail ORA-00922: missing or invalid option.

This ORA-00922 itself shows only message code missing or invalid option. So many times people are confusing after got this typical Oracle error.

If we check error code from

We can see the message for correction of the same error is simple that check SYNTAX of executed command and correct it.

Sometimes we can get this error of missing comma (,) or missing bracket () or missing length of long datatypes. Sometimes also get the same error due to invalid datatype define in PL/SQL code. The main and important thing is only that wrong SYNTAX using in coding or SQL execution.

All the best,

Thanks and regards,

Dbametrix Solutions

Expert Remote DBA team with low-cost remote plans.

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.