-
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
Thanx
Sam
Life is a journey, not a destination!
-
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
Thanx
Sam
Life is a journey, not a destination!
-
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
Thanx
Sam
Life is a journey, not a destination!
-
Thanks a lot Sam. These examples really helped a lot.
Shiva.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|