Re: COMMIT in for loop

Brett Neumeier wrote:
>
> amyleone_at_my-deja.com wrote:
> > In article <376AA2B5.B5A1CED5_at_lucent.com>,
> > Kenneth C Stahl <kstahl_at_lucent.com> wrote:
> > > It may be a rollback segment problem.
>
> > You must be right because when I try it again I get:
>
> > ERROR at line 1:
> > ORA-01555: snapshot too old: rollback segment number 8 with name
> > "RBSLRG07" too
> > small
>
> The problem you are encountering is caused by fetching from a
> cursor after doing a commit. When you perform a commit, you
> should close and re-open all cursors. Oracle's problem report
> number 1005107.6 describes the situation rather completely.
>
> This has some implications on how you write your code. Supposing
> that you are performing some logic on each record returned by a
> particular cursor; to resolve the problem, you should find some
> way of breaking the records returned by the cursor into chunks
> using cursor parameters, and then do a "commit" after each chunk.
>
> That is, if your current process has a cursor:
>
> cursor my_big_wad_of_records
> is
> select foo, bar, baz
> from record_source;
>
> and "foo" is an indexed number column, for example, with values
> between 1 and 50000, you could have:
>
> cursor piece_of_the_big_wad (low integer, high integer)
> is
> select foo, bar, baz
> from record_source
> where foo between low and high;
>
> and then loop through the cursor ten times, starting with low := 1
> and high := 5000; and successively setting low := high + 1 and
> high := low + 5000.
>
> -bn
> sacq_at_usa.net

Ok, what about this.

Say I have an outer cursor that is selecting parent records. I take the key value from the parent record and use it to delete child records with that key. I then delete the parent record using the rowid that I got as part of my outer cursor. I keep this up and eventually I get the snapshot error message (actually, this is a real situation - I have a delete routine like this and about every 3-4 days I see this error). What if I prior to opening the outer cursor I perform a SET TRANSACTION USING ROLLBACK SEGMENT XXX then when I get ready to perform deletes I perform another SET TRANSACTION USING ROLLBACK SEGMENT XXX using a different rollback segment. Would this solve the problem?

It also works out that I probably could re-write the program so that it would store all the relevant information for the "outer" cursor in pl/sql tables. Then once that cursor was closed I'd sweep through the table and perform the deletes. That way I wouldn't be selecting from a cursor after a select. Since I only delete about 8000-10000 parent rows in any given evening, I'd probably be ok since I have heaps and gobs of available memory.

Ken Received on Tue Jun 22 1999 - 20:19:44 CDT