ORA-00959: tablespace 'TEMP' does not exist

Hi all,

I am getting a
ORA-00959: tablespace 'TEMP' does not exist
when I try to run a query.

I checked the assigned default & temporary tablespace for the user and 'TEMP' is neither of them. In fact there is no tablespace called 'TEMP' in the database at all. Also I have not mentioned any tablespace name in the query.

Does anyone know what is causing this error ?
Any ideas greatly appreciated.

Thanks,
Shiva.

Does the instance have a TEMP tablespace? recheck the user's TEMPRORY tablespaace and confirm that the tablespace is of temprory one.

Sam

Hi Sambhavan,

I checked the user and its default tablespace is called 'PROVISION' and the temporary tablespace is called "DRSYS'. And there is no tablespace called 'TEMP' in the instance at all. Also I am having this problem only from today noon.

This makes things all the more confusing for me.

Is your tablespace DRSYS is of temprory type?

Sam

No it is of type Permanent. Does it have to be of type 'Temporary'. Then why did I not get any errors before ?

Thanks for your continued help.

Curtecy Oracle Metalink:


Problem Description
-------------------

A user tries to create an index and gets the following error:

SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist


Solution Description
--------------------

The DBA has to check the TEMPORARY TABLESPACE defined for the user performing
the CREATE INDEX statement whichever is the owner of the index.

SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT TEMP


If the tablespace does not exist anymore:

* either recreate the tablespace
* or change the user's TEMPORARY TABLESPACE to an existing one


=============================================================================
Example1: Connect under SCOTT whose TEMPORARY TABLESPACE is TEMP
=============================================================================
SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT TEMP

SQL> connect scott/tiger
Connected.
SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

SQL> connect system/manager
Connected.

SQL> CREATE TABLESPACE temp DATAFILE '/8i/ora816/oradata/V816/temp01.dbf'
2 SIZE 1M;
Tablespace created.

SQL> connect scott/tiger
Connected.
SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
Index created.


=============================================================================
Example2: Connect under U1 whose TEMPORARY TABLESPACE is TEMP2
=============================================================================
SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
U1 TEMP2

SQL> connect u1/u1
Connected.

SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP2' does not exist

SQL> ALTER USER u1 TEMPORARY TABLESPACE temp;
User altered.

SQL> connect u1/u1
Connected.

SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
Index created.

=============================================================================
Example3: Connect under SYSTEM whose TEMPORARY TABLESPACE is SYSTEM
=============================================================================
SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM SYSTEM

SQL> connect system/manager
Connected.

SQL> CREATE INDEX scott.i_empno ON scott.emp (empno) TABLESPACE indx;
Index created.


Explanation
-----------

The creation of an index requires a SORT operation.
If the SORT operation cannot be performed in memory (due to a SORT_AREA_SIZE
too small for example), then a tablespace for sorting is required.
The tablespace used is the one defined as TEMPORARY TABLESPACE for the user
performing the CREATE INDEX operation.
The tablespace does not exist anymore, and the sort operation cannot be
performed.



Sam

Thanks a lot Sam. These examples really helped a lot.

Shiva.