Re: Snapshot too old error revisited

Richard Hansen wrote:
>
> Previously, someone posted a problem that they were having with the
> ORA-01555 (Snapshot too old error). I have been having the same
> problem and followed the advice of those who responded to the original
> post. I am still occasionally having the problem and would like to
> present my problem and beg for help.
>
> I am inserting about 3.5 million records from one table into another.
> I have to process each record a bit before insertion so have declared
> a cursor on the source table and fetch each record, process it, and
> insert into the destination table. Every 100,000 rows I commit and
> continue.
>
> The last time I ran this it blew after inserting 300,000 rows. Oh
> yeah, in preparation for this I created a pretty big rollback segment
> (~ 1 gig) and at the beginning of the script as well as after the
> commit I perform a "alter session use rollback segment ..." so that I
> can utilize this big rollback segment.
>
> Last week I ran this during business hours and it ran successfully. I
> ran it yesterday evening (Saturday) and it ORA-1555'ed as mentioned
> above.
>
> What am I missing? Any suggestions?
>
> Thanks in advance for any direction.

One approach that may help is to determine whether you have a single SELECT cursor that is being fetched from through the whole process. If so, remeber that it must read the version of the table(s) which existed at the start of the whole process, which means that *all* before images must be retained. This is by far the most common reason for the problem you describe.
If such is the case, see if the process can be broken into discrete chucks (reading, say, data for one country / factory / currency at a time) and closing the cursor before executing the next select. Note that manipulating the commit frequency does not help at all in the above case.

Chrysalis. Received on Sun Mar 23 1997 - 00:00:00 CST