OracleTemp & undo

Temporary tablespace usage

Size, allocated and free space for each temporary tablespace.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN tablespace_name HEADING "TABLESPACE"   FORMAT A204COLUMN size_gb         HEADING "SIZE GB"      FORMAT 99,990.995COLUMN allocated_gb    HEADING "ALLOCATED GB" FORMAT 99,990.996COLUMN free_gb         HEADING "FREE GB"      FORMAT 99,990.997 8SELECT tablespace_name,9       ROUND(tablespace_size / POWER(1024, 3), 2) AS size_gb,10       ROUND(allocated_space / POWER(1024, 3), 2) AS allocated_gb,11       ROUND(free_space / POWER(1024, 3), 2) AS free_gb12FROM dba_temp_free_space;

Save it as ora-temp.sql and run it with SQL> @ora-temp.

Open in denrepo

Helps with

• ORA-01652: unable to extend temp segment by %s in tablespace %s

Part of these runbooks

• Space emergency

More Oracle scripts: Temp & undo

• Sessions using temp spaceWho is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
• Undo usage and ORA-01555 riskUndo extents by status, then the longest query and any snapshot-too-old or out-of-space errors from V$UNDOSTAT (roughly the last four days).
• Open transactions and the undo they holdEvery open transaction with its session, undo size and start time. An old transaction from an idle session is usually someone who forgot to commit.