How to Resolve ORA-00905: missing keyword

ORA-00905

ORA-00905 is a very broadly used error message, any expected keyword missing from its statement will result in ORA-00905. These are only cases we met.

ORA-00905 means that an expected keyword is missing from the statement at the specific position of statement, usually, it's a syntax error.

In reality, this error has widely been seen in many statements if there's any of the following problems:

• Missing keyword
• Misspelling keyword

SQL parser always knows what keyword should be there. If your case is neither of above problems, you may leave a comment to this post.

Let's see some error patterns.

1. Create Table
2. Create Index
3. Create View
4. Alter Tablespace
5. Alter Database
6. Grant Privilege

Create Table

NOT NULL

SQL> create table fruits (fruit_name varchar2(20) not, price number);
create table fruits (fruit_name varchar2(20) not, price number)
                                                *
ERROR at line 1:
ORA-00905: missing keyword

In this case, we missed NULL keyword.

SQL> create table fruits (fruit_name varchar2(20) not null, price number);

Table created.

DOUBLE PRECISION

SQL> create table fruits (fruit_name varchar2(20) not null, price double);
create table fruits (fruit_name varchar2(20) not null, price double)
                                                                   *
ERROR at line 1:
ORA-00905: missing keyword

In this case, we missed PRECISION keyword.

SQL> create table fruits (fruit_name varchar2(20) not null, price double precision);

Table created.

Create Index

SQL> create index birth_date_idx employees(birth_date);
create index birth_date_idx employees(birth_date)
                            *
ERROR at line 1:
ORA-00969: missing ON keyword

In this case, we missed ON keyword.

SQL> create index birth_date_idx on employees(birth_date);

Index created.

Create View

SQL> create view happy_employees select * from employees where salary >= 10000;
create view happy_employees select * from employees where salary >= 10000
                            *
ERROR at line 1:
ORA-00905: missing keyword

In this case, it turns out that we missed the keyword AS in the statement.

SQL> create view happy_employees as select * from employees where salary >= 10000;

View created.

Alter Tablespace

SQL> alter tablespace example add '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.dbf' size 10m autoextend on next 10m maxsize unlimited
                             *
ERROR at line 1:
ORA-00905: missing keyword

In this case, we missed the keyword DATAFILE in the statement.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

Alter Database

SQL> alter database move tempfile 1;
alter database move tempfile 1
                    *
ERROR at line 1:
ORA-00905: missing keyword

You can move a datafile online, but you can't move a tempfile like that. To move a tempfile, you can just add a tempfile and drop the original one.

Grant Privilege

SQL> grant select any table hr;
grant select any table hr
                       *
ERROR at line 1:
ORA-00905: missing keyword

In this case, we missed TO keyword.

SQL> grant select any table to hr;

Grant succeeded.

Keywords

To correctly use keywords, you can query the dynamic dictionary V$RESERVED_WORDS for sure.

Reserved Keywords

SQL> select keyword from v$reserved_words where reserved = 'Y' order by 1;

Oracle Keywords

SQL> select keyword from v$reserved_words where reserved = 'N' order by 1;

Don't worry about the error ORA-00905 too much, it always points out the position where keyword missed. Another similar error ORA-02142 might also be thrown in ALTER TABLESPACE ADD DATAFILE statements.