8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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...