Re: PL/SQL cursor

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