Temporary Tablespace Enhancements in Oracle Database 11g

~ Temporary Tablespace Enhancements in Oracle Database 11g onwards
~ 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.







Note: If you are doing in prod database, do it in off peak hours and reset application server services.