Re: CURSORS AND ROLLBACK SEGMENTS

> Hi, I have a CURSOR which selects around 30,000 rows. I then process the rows
> returned by the CURSOR. After processing each row, I do a commit. THe stored
> procedure seems to process around 10,000 rows and then throws up an error
> saying that the rollback segment is too small. No other errors are generated
> by the procedure. My understanding was that if we do a commit after each row
> is processed, there is no reason for the rollback segment to be filled. Am I
> missing something here?? ANy help would be appreciated. Thank You, rgds
> Jayadev

What was the ORA error number for this? ORA-1555?

Rollback data not only used to roll back a transaction, it is also used to
reconstruct the original contents of a table for read-consistency. When you
open a cursor on a table, Oracle will return the rows that were in the table
at the time the cursor was opened. So it needs to preserve any rollback data
for that table until the cursor is closed, so it can re-create the original
data for each FETCH.

From my understanding, the rollback data is kept after the commit. When Oracle
runs out of rollback space, it will start overwriting the data (and hope that the
cursor doesn't ask for it). If the cursor does ask for it, you will get ORA-1555: snapshot too old / rollback segment too small.

This *should* only be a problem if the rows you are modifying have not been
read by the cursor yet. I had a customer once that was experiencing this
error. She is an SQL beginner, and her UPDATE statement had no WHERE clause. So she was modifying the entire table on each UPDATE. For an 800 row
table, the first fetch generated 799 rows of rollback data that needed to be kept. The second fetch generated 798 rows, the third 797, etc...

Hope this helps.

-Mike Received on Fri Apr 02 1999 - 17:07:12 CST