Resolve ORA-02180: invalid option for CREATE TABLESPACE

While creating tablespace if you’re getting the error ORA-02180, then this article is for you which will help you to resolve this. Mainly this is a syntax error. So this may occur in three cases:

Case 1: Tablespace name is missing while tablespace creation

You may have missed the tablespace name to give while creating tablespace like below :


SQL> create tablespace datafile '/u01/app/oracle/oradata/ORCLCDB/orcl01.dbf' size 100m autoextend on;
create tablespace datafile '/u01/app/oracle/oradata/ORCLCDB/orcl01.dbf' size 100m autoextend on
                           *
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE

SQL>

Solution : Mention the tablespace name.


SQL> create tablespace ORCL datafile '/u01/app/oracle/oradata/ORCLCDB/orcl01.dbf' size 100m autoextend on;

Tablespace created.

SQL>

How to Resolve ORA-02180: invalid option for CREATE TABLESPACE

Case 2: Temporary Tablespace name is missing while Temp tablespace creation :

You may have missed the temp tablespace name also while creating the temporary tablespace like below :

SQL> create temporary tablespace tempfile '/u01/app/oracle/oradata/ORCLCDB/tempts_01.dbf' size 100M;
create temporary tablespace tempfile '/u01/app/oracle/oradata/ORCLCDB/tempts_01.dbf' size 100M
                                     *
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE

Solution: Mention the temporary tablespace name.


SQL> create temporary tablespace TEMP_TS tempfile '/u01/app/oracle/oradata/ORCLCDB/tempts_01.dbf' size 100M;

Tablespace created.

Output :

Case 3 :

You may have missed any keyword while creating the tablespace or temporary tablespace. Like here, in place of create tablespace command, we have put add command by mistake, so syntax should be correct. Similarly, you could miss any other keyword as well. Please look into that too if the whole syntax is correct.