RE: Strange snapshot too old during a select statement
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2004 01:59:17 -0700
I'll wager that what is happening is the following:
1) Your long SELECT starts and the system notes that the system change
number (SCN) is x
2) Another transaction comes along and updates a row. The current
version of the row has a SCN of x+1. The old version of the row, the one from
SCN x, is stored in the rollback segments (or UNDO)
3) At some point, your SELECT needs to read this block as of SCN x, so it
has to visit the rollback segment to get the old version of the data.
4) When Oracle visits the rollback segment, it finds that the data has
been overwritten. Since Oracle cannot reconstruct a picture of the data at SCN
x, it throws the ORA-01555 error.
If you were using UNDO, this would be a bit easier to deal with-you would set
the undo retention time to the length of your query and ensure that you throw
enough disk in to handle the UNDO generation for that amount of time. When
you're dealing with rollback segments, though, you have to figure out why the
data is no longer available when the query wants it. You may have too few
rollback segments, you may have rollback segments whose OPTIMAL values are too
small, your rollback segment size may be too small.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of NGUYEN Philippe (Cetelem)
Sent: Thursday, March 11, 2004 1:46 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Strange snapshot too old during a select statement
Importance: High
Hi list!
we encounter a very strange "ORA-01555 : snapshot too old :rollback segment
number 3 with name "_SYSSMU3$" too small"
This is a very long query
With a query that only make a select statement.
We have open a TAR with Oracle Support but they say that it's not possible
since this only a "select" statement ?!!
Any idea ?
TIA
Philippe
Other related posts:
