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

Oracle
  1. Create Temporary Tablespace Temp

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

  1. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

  1. 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;
  2. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

  1. 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;

  1. 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