| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CURSORS AND ROLLBACK SEGMENTS
A copy of this was sent to jgopinath_at_gt.com
(if that email address didn't require changing)
On Fri, 02 Apr 1999 20:28:55 GMT, you wrote:
>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
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
It is exactly because you are commiting that you are getting:
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
You must be updating the table you are querying. This error is saying
"Hey, I cannot process your query anymore because the data you need to read has been changed by some other transaction and I can no longer locate that data in the rollback segment to reconstruct the block you need as it appeared when your query began".
Get rid of the commit in the loop and the query will be able to process. Its not that the rollback segment is getting filled -- its because you are *not* filling the rollback segment you are getting this error. If you remove the commits and get an error about failure to EXTEND the rollback segment, then the rollback segments are too small to hold your transaction and you will need to increase them.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |