how to solve ora-01555 when you have a loop with frequents updates

http://wayback.archive.org/web/20091027013758/http://geocities.com/bobbyandmarielle/

You really can find a ton of good stuff on Oracle's support web site, Metalink.  I found a terrific trick in NOTE:45895.1 which is titled "ORA-01555 "Snapshot too old" in Very Large Databases"  Look at the section numbered Solution 4c that refers to the issue of getting snapshot too old errors when you have a "Fetch across commit" situation.

Essentially, you can eliminate an ORA-01555 snapshot too old error in code where you are fetching from a select statement in a loop and within the loop you are updating tables and periodically committing.  The trick is to add an order by to your select statement that creates a temporary segment to sort the results.  The order by has to be on a column that isn't indexed or else a temp segment won't be created. 

In our case were weren't getting ORA-01555 errors, we were just spending hours reading from the rollback segments while executing the outer loop.  I added an order by on some random non-indexed column and voila - all our rollback segment read waits went away.  Our select went from running 14 hours to 19 minutes. 

The key point here is that selects from temporary segments don't use rollback segments.  I haven't tried it, but I assume that temporary tables would have the same effect.  i.e. load a temporary table with your data that you want to loop over and then you won't get any rollback segment waits or snapshot errors as you are processing your updates and commits within the loop.