How to drop and recreate TEMP Tablespace in Oracle 11g

Today we had one request to shrink temp tablespace ..For this task we dont need any downtime.

High level steps:

a. Create tablespace temp2
b.Make temp2 as default
c.Drop tablespace temp
d.Make tablespace temp
e.Make temp as default.
f.Drop temp2 tablespace.


Output:

1.Create Temporary Tablespace Temp2:

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  '/backup/JAY_TMP/temp01.dbf' SIZE 4096M;

Tablespace created.

2. Move Default temp tablespace as temp2

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


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; 

Kill the sessions.


4. Drop temp tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

5. Recreate Tablespace Temp

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/backup/JAY_TMP/temp1.dbf' SIZE 4096M,'/backup/JAY_TMP/temp2.dbf' SIZE 4096M;

Tablespace created.

6 Move Tablespace Temp, back to new temp tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

7. Drop temporary tablespace temp2:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


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.