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
Data Pump Impdp
When we imported some tables from the source by data pump impdp, we saw ORA-00959 and ORA-39083.
[oracle@test ~]$ vi /backup/datapump/impdp.log
...
ORA-39083: Object type TABLE:"ERPAPP"."PAY_HIST" failed to create with error:
ORA-00959: tablespace 'ERP_TBS_09' does not exist
We'll talk about the solution to ORA-00959 impdp next.
Solutions
To conquer ORA-00959, we divided into several situations to explain solutions.
Use Current Tablespace
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.
Create New Tablespace
Whenever necessary, please create new tablespaces for your job.
ORA-00959 Impdp
First of all, we need to know which tablespaces are missing from the target database.
[oracle@test ~]$ grep "ORA-00959" /backup/datapump/impdp.log | uniq
ORA-00959: tablespace 'ERP_TBS_08' does not exist
ORA-00959: tablespace 'ERP_TBS_09' does not exist
By using command uniq, we can condense all ORA-00959 errors into several line. As we can see, there're 2 tablespace are missing from the target database.
To overcome missing tablespace, we can either create the same name tablespaces in the target database, just like we said previously, or we can remap all missing tablespaces to current ones when importing data.