Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL cursor
My suspicion is that the <delete from tables> in the loop is deleting from the table(s) referenced by cursor C1 (i.e. delete <table where current of C1). In this case, the advice to reduce commits is good, but may not be sufficient, since a new rollback version of all pages affected must be written for each commit in order to maintain read-consistemcy for C1 (thus flooding the rollback segment). A much better solution would be to defer the deletes until the end of the cursor loop and then delete using the same <where> clause as cursor C1.
Hope this helps.
Chrysalis.
Martin Purbrook wrote:
>
> In article <859422467.5035_at_dejanews.com>, ttran_at_ssofa.com wrote:
> >Could anyone explain to us where does the PL/SQL cursor data stored?
> >is it in the SGA or else where?
> >
> >Our archived/purge script looks something like this:
> > For C1 in Cursor_1
> > Loop
> > Process data (insert into archive tables and delete from tables)
> > commit;
> > End Loop;
>
> In this particular case, in the database. Each pass through the FOR loop
> fetches the next row from a read-consistent copy of your data, which is
> maintained in a rollback segment.
>
> >
> >Notice: the commit command is in the loop. Also this script constantly
> >getting error: Snapshot too old and rollback segment too small.
>
> The read-consistent view is being overwritten in the rollback segment. This
> is a common problem in PL/SQL if you commit changes to data in a cursor loop
> based on that same data. It may be the case that the rollback segments really
> are too small, but this type of looping contruct is always vulnerable to
> having it's read-conistent view trampled.
>
> You could partition the script to work on less data, and run multiple copies
> at different times.
>
> >
> >Thanks in advance, any help will be appreciated.
>
> MPP
>
> Martin Purbrook +44 1473 605208
> martin_at_kbss.bt.co.uk (FAX)+44 1473 622451
Received on Thu Mar 27 1997 - 00:00:00 CST