ORA-01555 Snapshot Too Old: Reproduce It, Then Make It Impossible
The nightly report has run for 55 minutes when it dies: ORA-01555: snapshot too old: rollback segment number … too small . You didn't change the query. The data is fine. Run it again at 2 a.m. and it works. So you shrug, add a retry, and move on — until it fails during the quarter-end close and someone asks why. Here's the part that flips the whole problem on its head: ORA-01555 is not your query…
The "snapshot too old: rollback segment too small" error in Oracle, represented by ORA-01555, occurs when a long-running query requires undo data that has been recycled due to insufficient undo space. This error is not due to a wrong query but rather insufficient undo configuration. To understand this better, it's essential to know that Oracle provides every query with a read consistency, which reflects a single point in time when the query began.
However, if the undo needed to rebuild the older block is no longer available, ORA-01555 is raised instead of returning data from different points in time. This situation typically arises when a long-running query runs concurrent with heavy DML activity, consuming undo tablespace space. The key to resolving this issue lies in ensuring that the undo tablespace is sized appropriately and configured with a retention guarantee, which prevents Oracle from overwriting unexpired undo data, thereby protecting the reader.
By adjusting the undo tablespace size and retention settings, the issue can be entirely eliminated, as demonstrated in the lab experiment.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.