ORA-00959: tablespace does not exist

I got ” ORA-00959: tablespace does not exist ”  error in Oracle database.

ORA-00959: tablespace does not exist

Details of error are as follows.

sql> alter tablespace test offline;
ORA-00959 tablespace test does not exist

tablespace does not exist

This ORA-00959 error is related with the related tablespace.

Check and query the related tablespace exist or not using the following scripts.

SQL> select * from v$tablespace where tablespace_name=’TEST’;

SQL> select tablespace_name from dba_tablespaces;

If it doesn’t exist, you can create it as follows.

If you are using file system for Oracle Storage, then you can use it following script.

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'/oracle/oradata/TEST/NEW_TBS_TEST01.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,
'/oracle/oradata/TEST/NEW_TBS_TEST02.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

If you are using the Oracle ASM , then you can use it following script.

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'+DATA' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

If you want to create temporary (temp) tablespace, you can create it as follows, if you use Oracle ASM.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

If you use File system, you can create the temp tablespace as follows.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
''/oracle/oradata/TEST/TEMP02.dbf' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

If you want to create undo tablespace, you can create it as follows, if you use Oracle ASM.

CREATE UNDO TABLESPACE UNDO2 DATAFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

If you use File system, you can create the undo tablespace as follows.

CREATE UNDO TABLESPACE UNDO2 DATAFILE 
'/oracle/oradata/TEST/UNDO02.dbf' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

If you want to create bigfile tablespace, you can create it as follows, if you use Oracle ASM.

CREATE BIGFILE TABLESPACE BIGTABLESPACE DATAFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE 34359738344K
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

OR If you got this error during IMPDP operation, then use the remap_tablespace as follows.

remap_tablespace=NON_EXISTING_TABLESPACE:NEW_TABLESPACE

remap_tablespace=TEST:USERS

OR

Sometimes tablespace is created with double quotes “” , then use its name with “” double quotes as follows.

SQL> alter tablespace "test" offline;
Tablespace altered.

Read the following post to learn more details about Tablespaces in Oracle.

https://ittutorial.org/create-tablespace-and-create-permanent-undo-and-temp-tablespace-add-datafile-tablespace-in-oracle/

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )