Question: How to resize the TEMPFILE(s) for a temporary tablespace after they have grown larger than needed?
Large sort operations can cause temporary tablespaces to grow very large and as such there may be a need to ‘downsize’ after such operations. Until Oracle 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.
In Oracle 11g, a new feature was added that can be used to shrink temporary tablespaces. This command can be used to shrink only 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 shrunk 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 attributes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.