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