Snapshot too old error revisited

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. Received on Sun Mar 23 1997 - 00:00:00 CST