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.
What is the Tablespace and How to Create Permanent, Undo and Temp 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 )