DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-00959: tablespace 'TEMP' does not exist

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.


  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Is your tablespace DRSYS is of temprory type?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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.

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    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!


  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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
  •  


Click Here to Expand Forum to Full Width