What causes ORA-01555?

A long-running pipeline query holds a read snapshot that expires before the query finishes. UNDO_RETENTION is set too low relative to query runtime. Heavy concurrent DML generates undo data that overwrites the undo needed for the long query. Small UNDO tablespace causes premature undo retention expiry

How do I fix ORA-01555?

Step 1: Increase UNDO_RETENTION to cover the longest query runtime: `ALTER SYSTEM SET UNDO_RETENTION = 3600;` (seconds).. Step 2: Increase the UNDO tablespace size so undo is not prematurely overwritten.. Step 3: Add GUARANTEE to the UNDO tablespace: `ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;`.. Step 4: Break the long-running pipeline query into smaller batches to reduce snapshot age.. Step 5: Schedule the long-running extract during low-DML periods to reduce undo pressure.

High severityperformance

Oracle Database Error:
ORA-01555

What does this error mean?

A query could not read a consistent version of a data block because the undo information needed to reconstruct the old snapshot was overwritten.

Common causes

1A long-running pipeline query holds a read snapshot that expires before the query finishes
2UNDO_RETENTION is set too low relative to query runtime
3Heavy concurrent DML generates undo data that overwrites the undo needed for the long query
4Small UNDO tablespace causes premature undo retention expiry

How to fix it

1Step 1: Increase UNDO_RETENTION to cover the longest query runtime: `ALTER SYSTEM SET UNDO_RETENTION = 3600;` (seconds).
2Step 2: Increase the UNDO tablespace size so undo is not prematurely overwritten.
3Step 3: Add GUARANTEE to the UNDO tablespace: `ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;`.
4Step 4: Break the long-running pipeline query into smaller batches to reduce snapshot age.
5Step 5: Schedule the long-running extract during low-DML periods to reduce undo pressure.

Example log output

ORA-01555: snapshot too old: rollback segment number 18 with name "_SYSSMU18$" too small

Frequently asked questions

How do I calculate the right UNDO_RETENTION value?

Set UNDO_RETENTION >= your longest running query's duration in seconds. Check v$undostat for the actual longest query runtime: `SELECT MAX(maxquerylen) FROM v$undostat;`.

Source · docs.oracle.com/error-help/db/ora-01555