How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Error :

SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files ;

select TABLESPACE_NAME, FILE_NAME from dba_temp_files

                                       *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 513 - see DBWR trace file

ORA-01110: data file 513: '+ASM_DATA'