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 )

 

 266 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply