See the following for a long but enlightening explanation: http://asktom.oracle.com/pls/ask/f?p=4950:8:2562091597295495465::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:275215756923,
Also search the same site, and Google, for 'snapshot too old'. Basically it's caused by other transactions modifying (and committing) changes to the data read by the long-running select query so much that Oracle can't give a result consistent to the point-in-time that the query started, because the old versions have been overwritten from the rollback segments (or in your case the undo tablespace since you're using automatic undo management - same principle though). This is the price you pay for Oracle's feature where reads are never blocked by concurrent writes. It may be an indication that: (a) Your undo tablespace is too small. If it were larger, the old versions wouldn't have been overwritten so quickly. (b) Your setting of UNDO_RETENTION is too low. Given sufficient space in the undo tablespace, if you set UNDO_RETENTION to be equal or longer to the time of the longest long-running query (in seconds), you won't get snapshot too old. UNDO_RETENTION is not a guarantee, as Oracle will rather break the undo retention time than deny a write access due to insufficient undo space. -- Andy Hassall <[EMAIL PROTECTED]> / Space: disk usage analysis tool <http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space> > -----Original Message----- > From: Rob Benton [mailto:[EMAIL PROTECTED] > Sent: 24 September 2004 22:16 > To: [EMAIL PROTECTED] > Subject: Error on large select statement > > Got DBI version 1.3.5 (not sure which version DBD::Oracle. > Is there a > way to tell?) on RedHat AS 2.1. > > I need some advice. I'm doing an oracle query on a table > that has over > 23 million records and I keep running into this error near > the end. I'm > not doing any updates, only a select. > > PrintError, RaiseError are on and AutoCommit is off. > > DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: > rollback segment number 19 with name "_SYSSMU19$" too small > (DBD ERROR: > OCIStmtFetch) at ./group_bb.pl line 144.
