Re: Oracle error 1555 snapshot too old

Hi Eric,

Your commit frequency could be the culprit, as Dave's article explains. By all means try to commit less frequently - no more than 20 times during the procedure (assuming a small database block size).

However, it could be that concurrent activity in other transaction is in part to blame also. For a fuller explanation of what is happening, and some possible approaches, see the tip on my web site (1st URL below) called "Avoiding ORA-1555 (snapshot too old) errors".

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/

On Mon, 06 Dec 1999 10:04:11 GMT, "Eric DUCHET" <eric.duchet_at_free.fr> wrote:

>Hi,
>
>I try to delete about 1 000 000 rows in my table with the following command
>
>CURSOR Cur_DeleteAll IS
> SELECT ROWID FROM ITEM WHERE (ITEMCODE, CODE, INSERTTIME,
>RECORDTYPE) IN
> (SELECT ITEMCODE, CODE, INSERTTIME, RECORDTYPE FROM
>TMPSPEITEMDEL);
>
>
> OPEN Cur_DeleteAll;
> LOOP
> FETCH Cur_DeleteAll INTO vRowId;
> EXIT WHEN Cur_DeleteAll%NOTFOUND;
>
> DELETE FROM ITEM WHERE ROWID = vRowId;
>
> Cpt_Commit := Cpt_Commit + 1;
> Cpt_LigEfface := Cpt_LigEfface + 1;
>
> IF Cpt_Commit = 1000 THEN
> Commit;
> Cpt_Commit := 0
> END IF;
> END LOOP;
>
>
>I 've got the Oracle message Snaptshot too old......
>
>Does anybody explain me why this message appears ?
>I commit my transaction every 1000 rows.
>I don't understand what's happening.
>
>Thanx
>
>
>
Received on Tue Dec 07 1999 - 06:15:45 CST