Skip to content

How to Resolve ORA-00959: tablespace does not exist

  • by

ORA-00959

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

SQL> alter tablespace example01 add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example01 add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-00959: tablespace 'EXAMPLE01' does not exist

ORA-00959 means that the tablespace you specified in the statement cannot be found in the database, you should check the name you provide or create the right one for it.

Solution

To solve ORA-00959, we should list all valid tablespace name by querying DBA_TABLESPACES and pick one for the statement.

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
EXAMPLE
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS

6 rows selected.

Chances are, you might go for the wrong database.

Once the tablespace name is confirmed, we can correct the statement and add the data file to it.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

We solved it.

Leave a Reply

Your email address will not be published.