Temporary Tablespace enhancements in oracle 11g with example

Temporary Tablespace enhancements in oracle 11g with example

oracle 11g introduce new view DBA_TEMP_FREE_SPACE for information about temprary tablespace

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                1965031424      916455424  1048576000

1 row selected.


For shrinking temporary tablespace

SQL> ALTER TABLESPACE temp SHRINK SPACE;
Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                916455424        916455424   0

1 row selected.

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

Tablespace altered.

If you omit the KEEP clause database will shrink the tablespace or tempfile to the smallest possible size.