Shrink temp tablespace size

Shrink temp tablespace size into Oracle DB 10g/11g:

1- Create anther temporary  tablespace 'temp2'

SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'D:\app\oradata\temp2_01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2. Move default Database temporary  tablespace to 'temp2'

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old 'temp' tablespace

 SQL> SELECT USERNAME, SESSION_NUM  FROM V$SORT_USAGE; 

       If the result contains any rows then your need to find the SID from the V$SESSION view, using SESSION_NUM from previous result set.

SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       
     Kill these sessions:

SQL> ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE; 

4. Drop 'temp' tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace 'temp'

SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE 'D:\app\oradata\temp01.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


6 Move Database temporary tablesapce, back to new 'temp' tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop tablespace 'temp2'

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;