Re: Oracle error 1555 snapshot too old

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 07 Dec 1999 21:18:23 +0800
Message-ID: <384D091F.3908@yahoo.com>

Eric DUCHET 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

You're modifying the data that the cursor is looking for - when you commit, that data quickly disappears from rollback segments giving snapshot errors...

If your rollbacks are big enough, remove the commits...If they are not, try this:

begin
loop
  delete FROM ITEM WHERE (ITEMCODE, CODE, INSERTTIME,  RECORDTYPE) IN
          (SELECT ITEMCODE, CODE, INSERTTIME, RECORDTYPE FROM  TMPSPEITEMDEL)
 and rownum < 50000;
 exit when sql%rowcount = 0;
  commit;
end loop;
end;

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Tue Dec 07 1999 - 07:18:23 CST