Re: Pro*C sqlca returns -1555

In article <53vahv$pnk_at_ramona.sfo.com>, Ian Parkin <twod_at_roxy.sfo.com> writes
>: The oracle support guy sent me an email that relates to rollback segments.
>: I completely rebuilt the 120 MB of rollback segments to no avail. There
>: is no-one else but me using the table. The table originally started out
>: as an import of a customer's 8.8 million rows. I have used the same
>: program with a different cursor to remove 3.3 million rows with no
>: problems. I have other criteria that need to be applied to the table
>: now to get it chopped down to what I need to send back to the customer.
 

>: Any thoughts?
>
>(base)->oerr ORA 1555
>01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too
>small"
>// *Cause: rollback records needed by a reader for consistent read are
>// overwritten by other writers
>// *Action: Use larger rollback segments
>
>Action text seems to indicate : Increase the size of the rollback segment or
>decrease the number of transactions between commits (write some chunk-blowing
>PL/SQL that removes 1 million rows at a time).
>
>: The opinions expressed above are my own, and may not
>: represent those of my employer.
>
>Under what circumstances would they be be representative ?
>
>IAP
I've had this problem a few times. The problem is with Oracle's read consistent model and block clean out, not with the actual size of the rollback segments, Oracle ensures best endevours for very low cost rather that perfection for high cost. You can confirm it is not the rollback segment by checking the high water mark on the rollback segment compared with the total amount of available space (it will be much lower). You have recreated the rollback segments so they cannot be corrupt.

It goes like this, you have a read cursor which opens and reads N records. You also delete records (less than N) as you work through the table. You then commit (freeing up the rollback segment space used by the uncommited transactions). However you carry on reading with the original cursor which is kindly trying to be read consistent, if it encounters one of the blocks that has had deletes applied to it (though different records), it will look in the rollback segments for the read consistent version (prior to the read cursor open). However as you have deleted, then commited again and again the space will have been freed and therefore the read consistent block/record will no longer be there as it has been over written by a subsequent delete. Phew, got that?

What can you do about it. Cry. Seriously either do everything in a single commit unit or reopen your read cursor either after every commit or be brave, catch the -1555 error code and reopen then.

Hope that works.

-- 
Chris Dipple, Production DBA, Royal Bank of Scotland