Re: Oracle error 1555 snapshot too old

This error is produced when a lot of update activity causes the rollback segment to wrap around and overwrite changes needed to reconstruct data. Basically the problem isn't one of too many updates between commits, or even too many commits, it's to do with the overall size of the query itself. You can get around it by getting bigger rollback segments or by breaking up the query into more manageable chunks. Try setting some sort of control loop to limit the number of records the cursor is returning.

Regards
Mark Brayshaw
Consultant,
Interim Technology
Level 4, 11 Harvest Terrace, West Perth WA 6005, AUSTRALIA Perth: Ph: +61 8 9481 0488 Fax: +61 8 9481 6576 Mobile: 0409 405 411
mailto:markbrayshaw_at_interim.com
http://www.interimtechnology.com.au

Transforming the way people work with technology with PERFORMANCE - TEAMWORK - LEARNING - INNOVATION - INTEGRITY

>Eric DUCHET <eric.duchet_at_free.fr> wrote in message
news:vSL24.422$Oc1.2807899_at_nnrp1.proxad.net...
> 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 - 22:55:50 CST