ORA-01555 database runtime_error ai_generated true

ORA-01555：快照过旧：回滚段号 名称为“_SYSSMU11_1234567890$”太小

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

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

其他格式: JSON · Markdown 中文 · English
80%修复率
85%置信度
1证据数
2023-09-15首次发现

版本兼容性

版本状态引入弃用备注
Oracle 19c active — — —
Oracle 21c active — — —
Oracle 23ai active — — —

根因分析

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

English

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

generic

官方文档

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

解决方案

  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;

无效尝试

常见但无效的做法:

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

    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% 失败

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