OracleTemp & undo
Sessions using temp space
Who is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A144COLUMN username FORMAT A155COLUMN program FORMAT A30 TRUNCATE6COLUMN sql_id FORMAT A137COLUMN tablespace FORMAT A128COLUMN segtype HEADING "USE" FORMAT A99COLUMN used_mb HEADING "USED MB" FORMAT 9,999,99010 11SELECT s.sid || ',' || s.serial# AS sid_serial,12 s.username, s.program, u.sql_id, u.tablespace, u.segtype,13 ROUND(u.blocks * t.block_size / 1048576) AS used_mb14FROM v$tempseg_usage u15JOIN v$session s ON s.saddr = u.session_addr16JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace17ORDER BY u.blocks DESC;Save it as ora-temp-sessions.sql and run it with SQL> @ora-temp-sessions.
Helps with
Part of these runbooks
More Oracle scripts: Temp & undo
- Temporary tablespace usageSize, allocated and free space for each temporary tablespace.
- 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.