Temporary Tablespace Enhancements in Oracle Database 11g Release

Temporary Tablespace Enhancements in Oracle Database 11g Release 1 [ID 1064846.1]

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

Modified 29-APR-2010 Type REFERENCE Status PUBLISHED

In this Document
Purpose
Scope
Temporary Tablespace Enhancements in Oracle Database 11g Release 1



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


Purpose
Manage Temporary tablespace more efficiently and the space allotted to the tablespace.
Scope
Customers and Support Analysts

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------- --------------- -------------- ----------
TEMP 134217728 134217728 133169152

With this information, you can perform an online shrink of a temporary tablespace using the ALTER TABLESPACE
command.

SQL> alter tablespace temp shrink space keep 100m;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------- --------------- --------------- ------------
TEMP 105906176 1048576 104857600

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/grdbms/32bit/app/oracle/oradata/S111W3/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------ --------------- --------------- ------------
TEMP 31522816 1040384 31457280

1 row selected.

The KEEP clause specifies the minimum size of the tablespace or tempfile. If this is omitted, the database will shrink the tablespace or tempfile to the smallest possible size.

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------- --------------- --------------- -------------
TEMP 2088960 1040384 1048576

Temporary Tablespace Enhancements in Oracle Database 11g Release 1
More information on Temporary Tablespaces:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces002.htm#i1013552