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 normal view: https://oracle-base.com/articles/11g/temporary-tablespace-enhancements-11gr1