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:
Table of Contents
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>
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.
So, here are the steps that can help to resolve the error ORA-02180.Also you can get more examples from the Oracle Forum Discussions as well.
Related ORA-error related Articles :
