Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Miscellaneous | Oracle RAC | Oracle Apps | Linux

Temporary Tablespace Enhancements in Oracle Database 11g Release 1

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>
For more information see:
Hope this helps. Regards Tim...

Back to the Top.