OracleTemp & undo

Undo usage and ORA-01555 risk

Undo 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).

Not yet verified. How scripts are tested

ora-undo.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN tablespace_name   HEADING "TABLESPACE"           FORMAT A204COLUMN status            FORMAT A105COLUMN mb                FORMAT 9,999,9906COLUMN extents           FORMAT 9,999,9907COLUMN since             FORMAT A168COLUMN longest_query_s   HEADING "LONGEST QUERY S"      FORMAT 9,999,9909COLUMN tuned_retention_s HEADING "TUNED RETENTION S"    FORMAT 9,999,99010COLUMN snapshot_too_old  HEADING "ORA-01555"            FORMAT 99,99011COLUMN out_of_space      HEADING "OUT OF SPACE"         FORMAT 99,99012 13SELECT tablespace_name, status,14       ROUND(SUM(bytes) / 1048576) AS mb,15       COUNT(*) AS extents16FROM dba_undo_extents17GROUP BY tablespace_name, status18ORDER BY tablespace_name, status;19 20SELECT TO_CHAR(MIN(begin_time), 'YYYY-MM-DD HH24:MI') AS since,21       MAX(maxquerylen) AS longest_query_s,22       MAX(tuned_undoretention) AS tuned_retention_s,23       SUM(ssolderrcnt) AS snapshot_too_old,24       SUM(nospaceerrcnt) AS out_of_space25FROM v$undostat;

CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN tablespace_name   HEADING "TABLESPACE"           FORMAT A20
COLUMN status            FORMAT A10
COLUMN mb                FORMAT 9,999,990
COLUMN extents           FORMAT 9,999,990
COLUMN since             FORMAT A16
COLUMN longest_query_s   HEADING "LONGEST QUERY S"      FORMAT 9,999,990
COLUMN tuned_retention_s HEADING "TUNED RETENTION S"    FORMAT 9,999,990
COLUMN snapshot_too_old  HEADING "ORA-01555"            FORMAT 99,990
COLUMN out_of_space      HEADING "OUT OF SPACE"         FORMAT 99,990

SELECT tablespace_name, status,
       ROUND(SUM(bytes) / 1048576) AS mb,
       COUNT(*) AS extents
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;

SELECT TO_CHAR(MIN(begin_time), 'YYYY-MM-DD HH24:MI') AS since,
       MAX(maxquerylen) AS longest_query_s,
       MAX(tuned_undoretention) AS tuned_retention_s,
       SUM(ssolderrcnt) AS snapshot_too_old,
       SUM(nospaceerrcnt) AS out_of_space
FROM v$undostat;

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

Open in denrepo

Helps with

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.
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.