Re: Oracle error 1555 snapshot too old

Sorry for the lateness in asking this. This seems to be saying that rollbacks are done on a block basis and not a row basis. Am I understanding it correcly?

Guess I better go back and read the manuals.

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:moen4s8f950qajppjk8k878k8fp676heto_at_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 Sat Dec 11 1999 - 07:45:09 CST