How To Shrink A Temporary Tablespace in 11G ?

How To Shrink A Temporary Tablespace in 11G ? [ID 452697.1]

--------------------------------------------------------------------------------

Modified 04-MAR-2009 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution



--------------------------------------------------------------------------------



Applies to:
Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.8
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.8
Goal
Huge sorting performed on a system can cause temporary tablespace to grow a lot and occupy most of the space on the file system. The sorts can occur occasionally and there is no need to keep a huge temporary tablespace all the time. Up until 11g there was no SQL command to release the unused allocated temporary space. One workaround for this problem is to create a new empty temporary tablespace with a smaller size, assign this new tablespace to the users and then drop the old tablespace. The disadvantage of this procedure is that it requires that no active sort operations are happening within the old temporary tablespace while it is being dropped.
Solution
In 11g there is a new SQL command that can be used to shrink the temporary tablespaces. This command can be used to shrink only the locally managed temporary tablespaces :



ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];


The SHRINK SPACE clause allows a user to shrink a temporary tablespace whereas SHRINK TEMPFILE allows the shrink of a temporary file. The optional KEEP clause defines the lower bound that a tablespace can be shrunken to. It is the opposite for MAXSIZE for auto extensible tablespace. If it is not specified, the system will try to shrink as much as possible as long as the other storage attibutes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option