Re: ERROR: snapshot too old

From: "Lasse Berntzen" >>
ORA-015555: snapshot too old: ... Every insertion into a table is followed by a commit. I thought a commit released the entries in the rollback segment. <<

When you get a 'snapshot too old' message it means that Oracle when attempting to perform a consistent read of a row is unable to locate the necessary data in the rollback segment. Oracle tracks changes at the block level so this block may well be one that your process changed and told Oracle it no longer needed when it commited.

The following applies if you are reading and updating the same table in one operation.

1. You may be able to avoid the error by commiting less often
2. If this fails then instead of reading and updating the same table in one step break the operation into two steps. In step 1 store the rowid and key columns in pl/sql tables, i.e., memory and then in step 2 update the original table. If the total number of bytes necessary to hold the key data is over a couple of meg then store the rows to be changed in a driving table. This technique can greatly improve performance by eliminating much of the I/O associated with rollback.

If you are not reading and updating the same table then you may need the total amount of always allocated rollback area to be increased. This is basically minextents X extent size X number of rollback segments in bytes.

I hope this helps.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Wed May 13 1998 - 18:29:04 CDT