Re: Rollback segment too old

In article <9fn9ac$3f8$1_at_news.netmar.com>, "Unknown" <peter.mckenzie_at_health.wa.gov.au> wrote:

>>This error is an application error and thus *NOT* logged at the instance
>>level. If your code has proper exception handling (and it probably
>>doesn't) you can trap this error quite easily.
>>
>>Hth,
>>
>>Sybrand Bakker, Oracle DBA
>>
>>
>>

> Once I trap it what will I do with it. How do you 'properly' handle > this error?

Well, first you must clearly understand what it means.

What's happening here is that you have a (relatively) long-running select statement, and the data that the statement is selecting is being somehow changed by DML from other processes. This error, ORA-1555 snapshot too old, rollback segment xxx too small, occurs when one of those DML transactions commits data and makes a part of rollback available to be recycled. Then another DML comes along and grabs that (now available) piece of rollback. Now, meanwhile, your long running select is collecting data, referring to rollback where necessary, and then hits a point where it can no longer rollback to a view of the data consistent w/ the start of the select, because the data was overwritten. This is 'snapshot too old.'

So, how do you handle it? Well, you could:

• restart the select. If DML activity has slowed, simply rerunning the query may be sufficient.
• Increase rollback tablespace and resize your rollback segments.
• Consider the possibility of tuning the long-running query, perhaps you can make it execute faster, which would make it less likely to run into ORA-1555.

From an application-level exception handling point of view, the only options that come to mind are restart the query or give up and error out of the process.

Hope this helps,

-Mark Received on Thu Jun 07 2001 - 20:09:55 CDT