ORA-01555 Explanation
There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a ‘read consistent‘ image. These are :o The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.o The transaction slot in the rollback segment‘s transaction table (stored in the rollback segment‘s header) is overwritten， and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.Both of these situations are discussed below with the series of steps that cause the ORA-01555. In the steps， reference is made to ‘QENV‘. ‘QENV‘ is short for ‘Query Environment‘， which can be thought of as the environment that existed when a query is first started and to which Oracle is trying to attain a read consistent image. Associated with this environment is the SCN (System Change Number) at that time and hence， QENV 50 is the query environment with SCN 50.CASE 1 - ROLLBACK OVERWRITTENThis breaks down into two cases: another session overwriting the rollback that the current session requires or the case where the current sessionoverwrites the rollback information that it requires. The latter is discussed in this article because this is usually the harder one to understand.Steps:1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 does some other work that generates rollback information.5. Session 1 commits the changes made in steps ‘3‘ and ‘4‘.
(Now other transactions are free to overwrite this rollback information)6. Session 1 revisits the same block B1 (perhaps for a different row).Now， Oracle can see from the block‘s header that it has been changed and it is later than the required QENV (which was 50). Therefore we need to get an image of the block as of this QENV.If an old enough version of the block can be found in the buffer cache then we will use this， otherwise we need to rollback the current block to generate another version of the block as at the required QENV.It is under this condition that Oracle may not be able to get the required rollback information because Session 1‘s changes have generated rollback information that has overwritten it and returns the ORA-1555 error.CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 commits the changes
(Now other transactions are free to overwrite this rollback information)5. A session (Session 1， another session or a number of other sessions) then use the same rollback segment for a series of committed transactions.These transactions each consume a slot in the rollback segment transaction table such that it eventually wraps around (the slots are written to in a circular fashion) and overwrites all the slots. Note that Oracle is free to reuse these slots since all transactions