Oracle Undo Usage and ORA-01555 Risk
Shows undo block status and snapshot-too-old error counts. Evaluates whether undo_retention is adequate.
SELECT status, ROUND(SUM(bytes) / 1048576, 1) AS size_mb
FROM dba_undo_extents
GROUP BY status;
SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
undoblks, txncount, maxquerylen, ssolderrcnt, nospaceerrcnt
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time DESC;ORA-01555 snapshot too old occurs when undo retention is shorter than the execution duration of long-running queries.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 11g).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
Other ORACLE Diagnostic Scripts
View All (100)Oracle Active Sessions and Wait Events
Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.
Find Blocking Sessions in Oracle
Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.
Oracle Tablespace Usage Including Autoextend
Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.
Top 20 Most Expensive Oracle SQL with v$sqlstats
Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.