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

ORA-02180

There're 2 basic patterns of error ORA-02180 in this post:

1. Missing Tablespace Name
2. Missing Keyword

A. Missing Tablespace Name

If we didn't specifiy the tablespace name in CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement, we got ORA-02180. The solution is simple, just provide a valid identifier for tablespace name in statement.

1. Normal Tablespace

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

We provided a valid idnetifier to it.

SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/tbs01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

2. Temporary Tablespace

SQL> create temporary tablespace tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited;
create temporary tablespace tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited
                                     *
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE

We provided a valid identifier to it.

SQL> create temporary tablespace systemp tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

B. Missing Keyword

The second error pattern is that we missed TEMPORARY keyword in the statement. Let's see the case.

SQL> create tablespace systemp tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited;
create tablespace systemp tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited
                          *
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE

We should add TEMPORARY keyword in the statement.

SQL> create temporary tablespace systemp tempfile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Another possibility is that, you might unintentionally take TEMPFILE for DATAFILE.