Skip to content

How to Resolve ORA-02142: missing or invalid ALTER TABLESPACE option

  • by

ORA-02142

Tried to add a new data file to a tablespace, but it failed with ORA-02142.

SQL> ALTER TABLESPACE EXAMPLE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER TABLESPACE EXAMPLE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
                                  *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

ORA-02142 means that something is missing from the specific position in your ALTER TABLESPACE statement, where it expects a keyword to complete the statement.

It turns out that we miss the keyword ADD 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.

Syntax errors may vary from one case to another, you may check How to Resolve ORA-00905: missing keyword for a reference.

Leave a Reply

Your email address will not be published.