9.8. Temporary Tablespace Features

Two new features are available in Oracle Database 11g related to temporary tablespaces. The first is the alter tablespace shrink space and alter tablespace shrink tempfile commands. Second is a new view, DBA_TEMP_FREE_SPACE. Let's look at each of these in some more detail next.

9.8.1. Temporary Tablespace Shrink

Certain database operations may require unusually large amounts of temporary tablespace usage, which leads to large tempfiles. If these operations are rare, or onetime operations, then it would nice to be able to shrink those tempfiles down to some smaller size. Oracle Database 11g provides two options to reduce the size of a temporary tablespace. The alter tablespace shrink space command will cause Oracle to reduce the overall size of the temporary tablespace to its originally defined size. The alter tablespace shrink tempfile command allows you to shrink a given tempfile to its originally defined size. Both commands come with a keep option, which allows you to indicate that you want the temporary tablespace or data file to be a minimum of the keep size.