ORA-01555 - Example

Here is a small example. Let's say we have a table with blocks 1, 2, 3, ... 1,000,000 in it.
The following is a serial list of events that could occur:


Time Action
0:00 Our query begins.

0:01 Another session UPDATEs block 1,000,000. Rollback information
for this is recorded into some rollback segment.

0:01 This UPDATE session COMMITs. The rollback data it generated is still there, but is now subject to being overwritten if we need the space.

1:00 Our query is still chugging along. It is at block 200,000.

1:01 Lots of activity going on, we have generated a little over 1.3 MB of rollback by now.

3:00 Our query is still going strong. We are at block 600,000 or so by now.

4:00 Our rollback segments start to wrap around and reuse the space that was active when our query began at time 0:00. Specifically, we have just reused the rollback segment space that the UPDATE to block 1,000,000 used back at time 0:01.

5:00 Our query finally gets to block 1,000,000. It finds it has been modified since the query began. It goes to the rollback segment and attempts to find the undo for that block to get a consistent read on it. At this point, it discovers the information it needs no longer exists. ORA-01555 is raised and the query fails.

Ref : Expert Oracle - Thomas kyte