Skip to content
Home » Oracle » How to Resolve ORA-00922: missing or invalid option

How to Resolve ORA-00922: missing or invalid option

  • Oracle

ORA-00922

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;

User altered.

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;

User altered.

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;

Table created.

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;

Table created.

Leave a Reply

Your email address will not be published. Required fields are marked *