The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 11:30 AM, Oracle ensures that all rows are as they appeared at 11:30 AM, even if the query runs until noon! Oracles does this by reading the "before image" of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:
ORA-01555: snapshot too old: rollback segment number string with name "string" too small Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables. However, you can set a super-high value for undo_retention and still get an ORA-01555 error. The ORA-01555 snapshot too old error can be addressed by several remedies:
▪ Re-schedule long-running queries when the system has less DML load
▪ Increasing the size of your rollback segment (undo) size
▪ The ORA-01555 snapshot too old also relates to your setting for automatic undo retention ▪ Don't fetch between commits