Re: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS06" too small

"Peter Wu" <pwu7_at_ford.com> wrote in message news:8ngv20$hm12_at_eccws12.dearborn.ford.com...
> Hi,
>
> I got this error and my stored procedure crashed. But the rollback
> tablespace has enough space and Oracle Alert file does not have any error
> message.
>
> Anyone can give a clue?
>
> Thanks
>
> Peter Wu
> Ford Motoe Co.
>
>

Hi Peter:

"Snapshot too old" is a classic DBA error. It's caused by the fact that new transactions have over-written the contents of a redo block that Oracle was still hoping to use for the preparation of a read-consistent image of the data.

For example - at 10:00am I update Bob's salary, dut don't commit at 10:01am, you run a report -select * from EMP;

That report needs to see Bob's OLD salary, given that I haven't committed my change yet. Your server process will find that old information in the rollback block that my transaction is using.

At 10:02 I commit my transaction, thereby freeing up my rollback block. Provided nothing overwrites that block, your report (which is still running!) will still be able to make use of Bob's old salary. But what we both forgot about was the fact that 10 o'clock is the busiest part of the day for other people to raise their transactions. Thus there is a rush of transactional information hitting the database, which causes the rollback segment to fill up, and wrap around on itself. My old rollback block is therefore overwritten -at which point your report fails with an ORA-1555 Snapshot too old error. It is totally irrelevant that there is heaps of space in the rollback TABLESPACE -it's the wrapping in the rollback SEGMENT that is the problem.

From that (somewhat simplified) explanation, you can see the possible cures. First, don't run reports or other things that depend on a read-consistent image of data in the middle of a busy transactional period. I bet your stored procedure will work fine if you run it at 2 o'clock on a Sunday morning!

Second, if running at that time is not an option, you'll have to try to ensure the rollback segment doesn't wrap around on itself, and overwrite the critical rollback blocks -and you can help ensure that by making your rollback segment larger -either give it more extents or make each extent bigger. That doesn't guarantee things, though, if you were to be on the receiving end of an abnormally high transactional load.

Failing that, check out this web page:
http://www.ixora.com.au/tips/admin/ora-1555.htm Here, Steve Adams explains how it is possible for your stored procedure to raise dummy blocking transactions in rollback segments, such that no-one is allowed to overwrite your important rollback blocks. Instead, the presence of the blocking transaction means the rollback segment will grow as needed for the duration of the procedure -it will of course shrink again when the blocking transaction is removed if you have OPTIMAL set. He has a couple of scripts there that will do the trick.

Hope that helps,
HJR
>
Received on Sat Aug 19 2000 - 06:52:03 CDT