Data Extract - Rollback Segment Error

[saw15]

Working with a web application, I am running extracts against an Oracle 8i database at various times a day. One extract in particular, pulls all of the updated claims data within the claims table.

At least 50% of the time, the file errors out with the following error:

ERROR:
ORA-01555: snapshot too old: rollback segment number 5 with name "R04" too small

I am the dba for this server / database, and am looking for recommendations that I could give the dba to eliminate the error.

I am also looking for some file checking, where if an error is identified in text file, then notification is sent out to admin.

Any help, pointers appreciated.

 

[saw15]

FYI, DBA has increased size of RB segment. Still getting error.

 

[alexhu]

Then he needs to make it bigger or go to

http://asktom.oracle.com/

search for "ORA-01555: snapshot too old"

and read the first post

"snapshot too old error Score(100) 11 Sep 2003 05 Jun 2000 3.3 years old"

and do what it says there depending on your circumstances

Alex

 

[SantaMufasa]

Frankly, "Tom's" suggestions (in the link that Alex suggested, specifically "

http://asktom.oracle.com/pls/ask/f?...SPLAYID,F4950_P8_CRITERIA:275215756923")

use the same FAILED remedy that Saw15's DBA has tried: Just make your RBS bigger. Tom toes the Oracle line, which is to make your RBS so big that they cannot possibly "roll over" from a large transaction.

Saw15's environment shows that you can hit "disk-storage bankruptcy" before you reach a RBS size (for ALL your rollback segments) that eliminates the "snapshot too old" error.

"Ask Tom" never mentions the solution we have successfully implemented that has eliminated (for YEARS) the "snapshot too old" error at our 60+ db sites, without our artificially (and unnecessarily) increasing the size of our rollback segments.

To read about causes and solutions to this error (that "Ask Tom" doesn't explain) you can go to our own Tek-Tips thread186-628107 (originated August 12, 2003, last updated September 12, 2003). Be sure to allow plenty of time and bring along a strong drink.

Dave

 

[rohanem]

You may be getting the error due to one more reason-
The database/tables against which u are running your extract may be getting updated.
Snapshot too old errors means that Oracle needs to know the original value of data that has been updated while the batch process has been running. The original data can sometimes be found in Oracle's rollback segments, but if the batch process takes too long the rollback segments might get overwritten.
Schedule your batch jobs at times of low activity. The less activity, the less updating, and hence the longer it takes for rollback data to get overwritten.