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.

The problem is not the rollback segments that your pro C program is using but the ones used by other users on the system.

Assume your pro C program starts running at 10.00 it will create read consitent views as of 10.00.
Soon after someone else comes along and modifies a block (block x), this will generate rollback in the normal way. This other user carries on happily generating rollback and eventually the rollback segment he is using will be overwritten and lost (This is the key point ).

Meanwhile you pro C is chugging along and tries to read block x, to create a read consistent view it goes to the rollback segment and finds that the correct block is not to be found - hence a snapshot to old error.

You need to :-

1) make your ProC work faster Or
2) Re open your cursor.
3) increase all of your rollback segments (probably just putting of
				            the inevitable)

      Nick Received on Fri Mar 24 1995 - 15:48:25 CET