Diagnosing ORA-01555 Snapshot Too Old in Oracle Database

Recognizable condition

ORA-01555 appears intermittently on long-running SELECTs, reports, or flashback queries while heavy DML is active. The query fails with snapshot too old, no data corruption is reported, and a retry often succeeds if it runs faster. The error is a read-consistency failure: Oracle cannot reconstruct the block version the query started with because the undo needed to roll it back has been overwritten.

Cause to symptom table

Finding	Typical symptom	Underlying mechanism
UNDO_RETENTION shorter than query duration	ORA-01555 on predictable nightly reports	Automatic undo management reuses extents after the retention window
Long active transaction holding undo	Errors spike while a batch update stays open	Undo blocks remain pinned until the transaction commits or rolls back
High undo generation rate	Errors during peak OLTP, short queries also fail	Retention window shrinks as undo blocks are consumed faster than they age out
Undersized undo tablespace	ORA-01555 plus ORA-30036 unable to extend segment	Tablespace fills, forcing premature reuse
Flashback query window exceeds undo	ORA-01555 on AS OF queries with large SCN offset	Flashback requires undo to be present for the requested time

Ordered checks

Run as a user with SELECT on V$ views, typically via SELECT_CATALOG_ROLE or DBA. Run in SQL*Plus or SQL Developer connected to the affected database.

1. Confirm the error context. Check alert log for ORA-01555 stack and identify session and SQL_ID. Risk: alert log access requires OS or SYSDBA.

   SELECT message_text, originating_instance FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-01555%';

2. Identify the failing session and SQL.

   SELECT s.sid, s.serial#, s.username, s.sql_id, q.sql_text
   FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id
   WHERE s.status = 'ACTIVE' AND s.sql_id = '';

   Expected check: match sid/serial# to the error timestamp.

3. Measure query length vs undo retention.

   SELECT name, value FROM v$parameter WHERE name IN ('undo_retention','undo_management');

   SELECT begin_time, end_time, maxquerylen, undoblks, unexpired_blks
   FROM v$undostat ORDER BY begin_time DESC;

   Expected check: maxquerylen is the longest query Oracle observed in seconds. Compare to actual elapsed time of the failing query.

4. Check active transactions and undo usage.

   SELECT s.sid, s.serial#, t.used_ublk, t.used_urec, s.logon_time
   FROM v$transaction t JOIN v$session s ON t.ses_addr = s.saddr
   WHERE t.used_ublk > 0;

   Long-running transactions pin undo and reduce available retention.

5. Review undo tablespace health.

   SELECT tablespace_name, file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 max_mb
   FROM dba_data_files WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name='undo_tablespace');

   Expected check: autoextend enabled and max size sufficient for peak load.

Fixes mapped to findings

Retention window too short

If maxquerylen < query elapsed time and undo tablespace has free space, increase UNDO_RETENTION. This is advisory unless undo retention guarantee is enabled.

ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

Risk: larger retention increases undo demand. Monitor V$UNDOSTAT for ORA-30036.

Long active transaction blocking reuse

If V$TRANSACTION shows a transaction open for hours with large used_ublk, coordinate with the application owner to commit or roll back. Killing a session can lose uncommitted work.

ALTER SYSTEM KILL SESSION ',' IMMEDIATE;

Risk: application impact and data loss for that transaction.

High undo generation rate

Break the long query into smaller chunks, add filters, or use a materialized view for reporting windows. This reduces the time a consistent read must be maintained.

Undersized undo tablespace

Resize or add datafiles with autoextend, or enable guaranteed retention for critical windows. Guaranteed retention can cause ORA-30036 if space is insufficient.

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;

Escalation criteria

Escalate to Oracle Support with trace files when:

• ORA-01555 recurs after undo sizing and retention changes
• Evidence of undo corruption or ORA-00600 appears in background processes
• SLA-critical batch jobs are repeatedly impacted and root cause remains unclear

Provide alert log excerpt, V$UNDOSTAT snapshot, and SQL_ID for the failing statement.

Limitations and verification

UNDO_RETENTION is not a guarantee under NO GUARANTEE mode; space pressure forces reuse. Increasing retention without sufficient undo tablespace size will not help.

Verify changes by re-running the failing query and confirming no ORA-01555 in alert log and successful completion. Compare V$UNDOSTAT MAXQUERYLEN to actual query elapsed time to ensure the retention window covers the workload. Monitor UNDOBLKS and UNEXPIRED_BLKS over peak load to confirm supply meets demand.

Version assumption: behavior described applies to Automatic Undo Management in Oracle Database 11g through 19c/23ai. Exact view names and parameter behavior are well established but verify in your release documentation.