Re: Oracle error 1555 snapshot too old

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 06 Dec 1999 08:43:09 -0500
Message-ID: <moen4s8f950qajppjk8k878k8fp676heto@4ax.com>

A copy of this was sent to "Eric DUCHET" <eric.duchet_at_free.fr> (if that email address didn't require changing) On Mon, 06 Dec 1999 10:04:11 GMT, you 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.

it is happening because you are committing your transaction every 1000 rows.

ORA-1555 occurrs when the read consistent mechanism employed by Oracle discovers that an old version data it needs is no longer available in the rollback segement -- we cannot return the read consistent answer to you.

The problem above is that when you opened your cursor -- the result set was 'pre-ordained'. Before we fetched the first row -- the answer was fixed at the point in time the query began. this is achieved by using the rollback (before images) segment to reconstruct blocks to look at they did when you query opened.

You are doing a "select ... from item where ( a, b, c ) in ( select ... )". This query is most likely using an index to read item. It will have to revisit the same block in the item table over and over and over as it processes the query. Lets say that the first record you read is on block 100. Also, lets say the 50,000'th record you read is on block 100. The first time you read block 100 - all is well, we don't need the rollback yet, we update the block -- generate rollback -- put it in the rollback segment and you continue one. Each time you process 1,000 records you commit (and by commiting you are saying "don't need that rollback -- feel free to overwrite it). By the time you got to record 50,000 -- the rollback you generated with record 1 is gone, you overwrote it with the 49,998 other updates you did. We can no longer reproduce the block you need for your query -- we through a 1555.

So, how to do the above and not get this? Many ways. My preferred way would be to size rollback so you don't have to commit. Short of that, a loop like:

begin
  loop

      delete from item where (itemcode,code,inserttime,recordtype)
            in ( select itemcode,code,inserttime,recordtype from tmpspeitemdel)
          AND ROWNUM <= 1000;

      Cpt_LigEfface := Cpt_LibEfface + sql%rowcount;      
      exit when sql%rowcount < 1000;
    
      commit;

  end loop;
  commit;
end;

That block does what yours did but will not suffer from the same issue (1555) as yours since it does not hold a query open across commits (number 1 cause of ORA-1555).
>
>Thanx
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 06 1999 - 07:43:09 CST