Site icon IT Tutorial

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.

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 )

 

Exit mobile version