OracleTemp & undo
Open transactions and the undo they hold
Every open transaction with its session, undo size and start time. An old transaction from an idle session is usually someone who forgot to commit.
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 status FORMAT A87COLUMN undo_mb HEADING "UNDO MB" FORMAT 9,999,990.98COLUMN undo_recs HEADING "UNDO ROWS" FORMAT 999,999,9909COLUMN started HEADING "TX STARTED" FORMAT A1610 11SELECT s.sid || ',' || s.serial# AS sid_serial,12 s.username, s.program, s.status,13 ROUND(t.used_ublk * (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') / 1048576, 1) AS undo_mb,14 t.used_urec AS undo_recs,15 TO_CHAR(t.start_date, 'YYYY-MM-DD HH24:MI') AS started16FROM v$transaction t17JOIN v$session s ON s.taddr = t.addr18ORDER BY t.start_date;Save it as ora-undo-sessions.sql and run it with SQL> @ora-undo-sessions.
Helps with
- ORA-01089: immediate shutdown or close in progress - no operations are permitted
- ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small
- ORA-30036: unable to extend segment by %s in undo tablespace '%s'
More Oracle scripts: Temp & undo
- Temporary tablespace usageSize, allocated and free space for each temporary tablespace.
- 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).