ORA-01555 database runtime_error ai_generated true

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

ID: database/read-committed-snapshot-too-old

Also available as: JSON · Markdown · 中文
80%Fix Rate
85%Confidence
1Evidence
2023-09-15First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Oracle 19c active — — —
Oracle 21c active — — —
Oracle 23ai active — — —

Root Cause

Oracle rollback segment contains needed read-consistent data that has been overwritten due to long-running query or insufficient undo retention.

generic

中文

Oracle回滚段包含需要的一致性读取数据，但由于长时间运行的查询或撤销保留不足而被覆盖。

Official Documentation

https://docs.oracle.com/en/database/oracle/oracle-database/19/errmg/ORA-01555.html

Workarounds

  1. 85% success Increase the undo tablespace size: ALTER TABLESPACE undo ADD DATAFILE '/u01/oradata/undo02.dbf' SIZE 10G; 
    Increase the undo tablespace size: ALTER TABLESPACE undo ADD DATAFILE '/u01/oradata/undo02.dbf' SIZE 10G;
  2. 75% success Optimize the query to fetch data in smaller batches or use COMMIT more frequently: BEGIN FOR rec IN (SELECT * FROM huge_table WHERE ...) LOOP ... END LOOP; END; 
    Optimize the query to fetch data in smaller batches or use COMMIT more frequently: BEGIN FOR rec IN (SELECT * FROM huge_table WHERE ...) LOOP ... END LOOP; END;
  3. 90% success Set undo_retention guarantee: ALTER TABLESPACE undo RETENTION GUARANTEE; 
    Set undo_retention guarantee: ALTER TABLESPACE undo RETENTION GUARANTEE;

中文步骤

  1. Increase the undo tablespace size: ALTER TABLESPACE undo ADD DATAFILE '/u01/oradata/undo02.dbf' SIZE 10G;
  2. Optimize the query to fetch data in smaller batches or use COMMIT more frequently: BEGIN FOR rec IN (SELECT * FROM huge_table WHERE ...) LOOP ... END LOOP; END;
  3. Set undo_retention guarantee: ALTER TABLESPACE undo RETENTION GUARANTEE;

Dead Ends

Common approaches that don't work:

  1. Increase UNDO_RETENTION to a very high value like 86400 seconds 70% fail

    Undo retention is only a target, not a guarantee; Oracle may still overwrite undo if tablespace is full or if undo tablespace is undersized.

  2. Set undo_management='MANUAL' to use manual rollback segments 90% fail

    Manual rollback segments are deprecated and can lead to worse ORA-01555 errors due to fixed segment sizes and no auto-tuning.