A report that has run for 45 minutes dies with:
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_..." too small
Or an overnight expdp export fails at 95%. The message blames a "rollback segment", but rollback segments haven't been managed by hand for 20 years. To fix it you need to know how Oracle gives queries a consistent view of the data.
Read consistency is built from undo
Oracle guarantees that a query sees data exactly as it was when the query started, even while other sessions change and commit rows underneath it.
When a block has changed since your query began, Oracle doesn't wait or lock. It rebuilds the old version of the block by applying the undo records that the changing transaction wrote. Undo lives in the undo tablespace, and after a commit that undo is only kept while space and UNDO_RETENTION allow.
ORA-01555 means: your query needed an old version of a block, and the undo required to rebuild it has already been overwritten. The longer a query runs and the more DML happens at the same time, the more likely this becomes.
The usual causes
-
Undo retention is too short for your longest query.
UNDO_RETENTIONdefaults to 900 seconds (15 minutes). With auto-extending datafiles Oracle tunes it upward on its own, but with a fixed-size undo tablespace it can only keep what fits. -
The undo tablespace is too small. Retention is best effort: when space runs out, Oracle overwrites expired, and eventually unexpired, undo so that current transactions can keep going.
-
Committing inside a loop that reads the same table ("fetch across commit"):
FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP UPDATE orders SET status id r.id; ; LOOP;
