Skip to content

How to Resolve ORA-00959: tablespace does not exist

  • by

ORA-00959

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.

Let's see some cases.

Add Datafile

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

Default Tablespace

Oracle always checks the existence of the tablespace before setting the default tablespace.

SQL> alter database default tablespace example01;
alter database default tablespace example01
*
ERROR at line 1:
ORA-00959: tablespace 'EXAMPLE01' does not exist

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, or mistake one PDB for another in a container database (CDB).

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/ORCLPDB/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. Required fields are marked *