Re: snapshot too old

: Dan Weinman (dano_at_bluedevil.cv.COM) wrote:
: : I have a pro*c program that is used to update selected records in a large
 table.
: : After processing about 400,000 records the program aborts with the
: : ORA-01555 error. The program declares a cursor for the select statement
: : and then fetches 500 rows at a time using the cursor. It then has the
: : possibility of updating any or all of the rows depending on the values of
: : the columns selected. A commit is done after the update and another 500
: : rows is fetched. This will continue until the error occurs.

In article <3krua6$g0f_at_news-2.csn.net> derby_at_csn.net "Gary Smith" writes:

:
: I had a similar problem in Oracle V6.0.37, which I solved by using the
: "set transaction use rollback segment SEG_NAME"
: after each commit.
:
: I'm not sure why this works and just a commit doesn't, however I do know it
: solved the problem. If anyone here knows WHY it works, I'd sure be in-
: terested.
:

An important point to note here is that Dan is doing something that he should _not_ be doing, i.e. commits inside a cursor loop.

In theory, a COMMIT invalidates all cursors, so Dan should not carry on reading from that cursor, he should re-open it and start again. (In practice, Oracle does not stop you from hurtling on through an invalid select cursor, but beware, the row you think you are deleting may not be the row is was when you selected it.)

The reason why Gary's solution may have worked is answered in part by the COMMIT, and in part by another poster to this thread: when the COMMIT takes place, the rollback segment in use for that transaction is released and Oracle will probably choose another one to write into. As the other poster said, you then find that at some stage you start over-writing your own old rollback. By issuing the Set Transaction command to keep on re-attaching to the same rollback segment (presumably a specially built large one) Gary has avoided the problem of writing over his own rollback.

-- 
Jonathan Lewis