Resize or Shrink Temporary Tablespace

If temporary tablespace size very large because of some bad queries or some other unexpected sorting operations, as maintanance DBA should shrink and reclaim that extra space. First, try resize the tempfile.

SQL> alter database tempfile '/oracle/oradata/ORCL/test01.dbf' resize 500M;

and faced error ORA-03297: file contains used data beyond request RESIZE value.

There are several methods to reclaim unused space depend on Oracle versions.

Method I	Database Version < 9i
	Database Version >=9i and related tablespace is not default temporary tablespace
Method II	Database Version >=9i and and related tablespace is default temporary tablespace
Method III	Database Version >=9i
Method IV	Database Version >=11g

Note: It is advisable to realize this operations during off hours. Otherwise  If tempfile is used then could not drop and give error ORA-25152: TEMPFILE cannot be dropped at this time. In this situation there are 2 scripts depend on oracle versions to find who use the temporary  segments.

Version <9i
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text

  FROM v$session a, v$sort_usage b, v$sqlarea c
  WHERE b.tablespace = 'TEMP'
  and a.saddr = b.session_addr
  AND c.address= a.sql_address
  AND c.hash_value = a.sql_hash_value
  ORDER BY b.tablespace, b.blocks; 
Version >=9i   
   SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE b.tablespace = 'TEMP'
     and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks;

Method I : 

Drop and recreate temporary tablespace.

SQL> DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M
3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Method II : 

Drop and recreate temporary tablespace when TEMP is default temporary tablespace.

If TEMP is default temporary tablespace and try to drop this tablespace like Method I faced error ORA-12906: cannot drop default temporary tablespace

In this situation steps like below

1. Create another temporary tablespace(create TEMP2)

2. Make new temporary tablespace is default (TEMP2 default)

3. Drop old temporary tablespace (drop TEMP)

4. Create temporary tablespace with old name (create TEMP)

5. Make new created tablespace is default (TEMP default)

6. Drop tablespace which created in step 1 (drop TEMP2)

SQL> CREATE TEMPORARY TABLESPACE TEMP2

2 TEMPFILE '/oracle/oradata/ORCL/temp02.dbf' SIZE 100M

3 AUTOEXTEND ON NEXT 10M MAXSIZE 2048M

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE TEMP 2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M 3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Method III : 

This method drop a tempfile. Only valid for >=9i version.First find tempfiles from v$tempfile and then drop

SQL> ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

Method IV: 

Shrink temporary tablespace and/or tempfile. Only valid for >=11g

ALTER TABLEPSACE TEMP SHRINK SPACE
or
ALTER TABLESPACE TEMP SHRINK TEMPFILE '................' KEEP 250M;