The Oracle (tm) Users' Co-Operative FAQ

I used 'set transaction use rollback segment xxx', but my query still encounters ORA-1555 on rollback segment 'yyy'.


Author's name: Mark J. Bobak

Author's Email: mark@bobak.net

Date written: 19th April 2002

Oracle version(s): 8.0, 8i

After using 'set transaction use rollback segment xxx', I still get ORA-1555 on rollback segment 'yyy' and I do not understand why.

Back to index of questions


The statement 'set transaction use rollback segment xxx' does two things:
1.) It begins a transaction.
2.) It tells Oracle that any DML for the duration of that transaction should write it's rollback to a rollback segment named 'xxx'.

However, the statement encountering the error is NOT DML.  It's a select statement.  A select statement will not write any data, so will not consume rollback.  A select statement will (attempt to) provide a read consistent view of the data.  In order to do so, it may have to refer to rollback.

Depending on what other transactions modified the data you are reading, those changes may have been recorded in ANY rollback segment.  If, in the course of constructing a read consistent view of the data, Oracle discovers data whose rollback has been overwritten, it will raise an ORA-1555. This error will report the name of the rollback segment from which the (now overwritten) consistent image cannot be reconstructed.

If you look at it this way, it's easy to see why the name of the rollback segment reported in the ORA-1555 error doesn't match the name of the rollback segment specified in the 'set transaction ...' statement.  It is a misconception to think that use of the 'set transaction use rollback segment xxx' statement can help you avoid an ORA-1555 error.  It's simply not the case.  Please see the items listed in the "Further reading" section below for more information about ORA-1555 and what actually can be done to avoid it.


Further reading:  ORA-01555 Snapshot too old - Detailed Explanation   (MetaLink Registration required)
                         Avoiding ORA-1555 Errors   (Link to IxOra, Steve Adams' website)


Back to top

Back to index of questions