Re: CURSORS AND ROLLBACK SEGMENTS: which one is correct ?

On Tue, 06 Apr 1999 15:28:20 -0600, Dogan Cibiceli <dcib_at_gwl.com> wrote:

>I have the same problem and I am not updating any row that I havenot touched
>yet. I am querying and updating the same table. So what is the proper answer ?
>

This is exactly the circumstance that is most likely to cause this error. Oracle ensures statement level read consistency by making use of the rollback segments.

As you update a row rollback information is written to the rollback segments. If you do not commit, the rollback segment blocks are not freed for re-use, the rollback segment will grow as required (subject to free space, maxextents etc.) and oracle will always be able to build a read consistent view.

If you commit, oracle releases the rollback space. Rollback space is allocated on a round robin basis (both across rollback segments and within them - if I remember aright), but eventually you will overwrite the rollback data for an earlier update. At that point, Oracle can no longer determine if the next row fetched is read consistent with respect to the opening of the cursor.

The best solution is to catch the error and re-open the cursor. Received on Wed Apr 07 1999 - 01:47:28 CDT