Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query from Hell! - ora-01555

Re: Query from Hell! - ora-01555

From: Hemant K Chitale <>
Date: Thu, 16 Nov 2006 10:00:00 +0800
Message-Id: <>

8.1.7 with manual Rollback Segments :

The commit doesn't make a difference to your query. COMMIT only commits the previous transaction in your current session. However, your query is failing to get a Read Consistent Image (ie as of the same SCN)
of the rows it requires from all the tables it is accessing. That means that either there are very many updates (update-and-commmit) to the underlying tables
by *other* sessions between the time your query begins and the time it get's to
the row which it is unable to reconstruct. Those *other* sessions should be using large rollback segments and/or there should be more rollback segments allocated for other transactions so that the probability of one transaction overwriting the committed RBS image of another transaction is reduced. Also, try unsetting OPTIMAL for RBSs that those transaction use so taht Oracle does not automatically (too frequently) overwrite "released" extents.

Another option is to tune the query so that it runs that much faster that it is able to recreate the images of the rows it needs quickly enough.

Another option is to run the query when the underlying tables are not being updated or have much less DML.

There are more esoteric reasons as well --- delayed block cleanout, insufficient number of transaction slots in the rollback segments, transaction slots being overwritten etc.

You can only try to reduce the probability of ORA-1555s.

See Notes 10630.1 and 40689.1


At 04:34 AM Thursday, you wrote:
>I have a query and even though I set transaction to a specific
>rollback segment, it gives me an error on another rollback segment.
>I cannot cycle the instance at all to set rollback segment only to
>the larger one.
>We are using Version 8.1.7. I have been thinking of using temporary
>tables. Here is what I am doing, any suggestions?:
>set transaction use rollback segment large_rbs;
>SELECT l.licensee_id
> , li.license_id

Hemant K Chitale

Received on Wed Nov 15 2006 - 20:00:00 CST

Original text of this message