Question

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

Answer 1

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.  Also see these important notes on commit frequency and the ORA-01555 error

The ORA-01555 snapshot too old error can be addressed by several remedies:

1.  Re-schedule long-running queries when the system has less DML load.

2. Increasing the size of your rollback segment (undo) size.  The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.

3. Don't fetch between commits.

Answer 2

The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10: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. From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter: