Temporary Tablespace Enhancements in Oracle Database 11g

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                                  56623104        56623104   55574528

1 row selected.

SQL>

Armed 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 40M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  42991616         1048576   41943040

1 row selected.

SQL>

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

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

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

1 row selected.

SQL>

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                                   1114112           65536    1048576

1 row selected.

SQL>