Re: HELP ORA-01555 - snapshot too old.....

"schponk_at_Weurk" <schponk_at_wanadoo.fr> wrote in message news:afcke1$beb$1_at_reader1.imaginet.fr...
> Hello !
> in a PL/SQL procedure, i've got a problem........... withe this ORA-01555
>
> in my procedure i've got :
>
> an update in a loop, this loop is conditionned by a cursor.
> the commit is, of course, before the end of the loop...
>
> a max of 80 Mb is commited in a loop.
>
> my rollback segment : 3 Gb.......... with an initial of 195
> Mb...................
>
> and I wonder why the ORA-01555 occurs..................................
>
> Thanks a lot for your help !!!!!!!!!!!
>
> ;oD
>
>

Because you commit in the loop.
You have a long running transaction, the before image of your select goes to the rollback segment.
You commit.
At that point the data is 'released', candidate to be overwritten. However, you are still selecting from the read consistent image of your select.
So once the select can't reconstruct the read consistent data anymore because these have been overwritten by subsequent transactions (whether your's or others).........

Bingo

Obviously you should commit outside the loop.

There are loads of materials on the web and in the archives of this group on ora-1555.
Please always research the archives.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address