Skip to content

How to Resolve ORA-00905: missing keyword

  • by

ORA-00905

Any expected keyword missing from statements may result in ORA-00905. This is only a case when we tried to add a new data file to a tablespace, but it failed with ORA-00905.

Case 1: Add a File to 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

Case 2: Grant Privilege to User

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

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

Solutions

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.

In case 1, it turns out that 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.

In case 2, we missed TO keyword.

SQL> grant select any table to hr;

Grant succeeded.

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 have to worry about the error ORA-00905, it always points out the position where keyword is missing. Another similar error ORA-02142 might also be thrown in ALTER TABLESPACE ADD DATAFILE statements.

Leave a Reply

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