How To Shrink A Temporary Tablespace in 11G ?

Applies to:

Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.2.0.2 – Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal

Huge sorting performed on a system can cause the 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 temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :

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 attibutes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.

© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Rollback Segment Utilization:Extent, Wrap and Shrink
2. Script:Tablespace Report
3. Function Based Indexes and Global Temporary Tables
4. Script to Detect Tablespace Fragmentation
5. SYSAUX Tablespace Grows Heavily Due To AWR
6. Troubleshooting a Database Tablespace Used(%) Alert problem
7. SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace
8. 11g MEMORY_TARGET Parameter Dependency