Shrink Temporary Tablespace 11g New Features

In 11g, enhancements has been made to Temporary tablespace.

In Previous Versions, if tempfile has grown big and needs to be resized, the procedure we used to follow is to create a new temporary tablespace and drop the bigger one.

In 11g onwards, Shrinking of temp files can be done and also we have new data dictionary table to find the Free space in the temporary tablespace.

##########################

#  Task 
##########################

Shrinking of Bigger Temp File.

##########################
#   Commands 
##########################

1.  Alter Tablespace temp shrink tempfile '+DATA/mydb/tempfile/temp.264.798205781' keep 5G; 
2.  Alter Tablespace temp shrink space; 

New Data Dictionary Table to Check Temporary Tablespace Free Space.

Select * from dba_temp_free_space;

##########################

## Solution
##########################