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.
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
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.
In this case, it turns out that we miss 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;
To correctly use keywords, you can query the dynamic dictionary V$RESERVED_WORDS for sure.
SQL> select keyword from v$reserved_words where reserved = 'Y' order by 1;
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.