ORacle switch TEMP tablespace

http://dbafix.blogspot.com/2010/08/how-to-drop-and-recreate-temp.html

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/oracle/oradata/bidb/tempX.dbf' 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/oracle/oradata/bidb/temp01.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited, '/u01/app/oracle/oradata/bidb/temp02.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;

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.