Strange snapshot too old during a select statement

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Strange snapshot too old during a select statement

Strange snapshot too old during a select statement

2004-03-11 - By Justin Cave (DDBC)

Reply: 1 2 3 4 5 6

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 (See ORA-01555.ora-code.com) 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@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of NGUYEN Philippe (Cetelem)
Sent: Thursday, March 11, 2004 1:46 AM
To: 'oracle-l@(protected) '
Subject: Strange snapshot too old during a select statement
Importance: High

Hi list!
we encounter a very strange "ORA-01555 (See ORA-01555.ora-code.com) : 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

<html xmlns:v= "urn:schemas-microsoft-com:vml " xmlns:o= "urn:schemas-microsoft-com:office:office " xmlns:w= "urn:schemas-microsoft-com:office:word " xmlns:st1= "urn:schemas-microsoft-com:office:smarttags " xmlns= "http://www.w3.org/TR/REC-html40 " >

<head >
<meta http-equiv=Content-Type content= "text/html; charset=iso-8859-1 " >
<meta name=Generator content= "Microsoft Word 11 (filtered medium) " >
<!--[if !mso] >
<style >
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style >
<![endif]-- >
<title >Strange snapshot too old during a select statement </title >
<o:SmartTagType namespaceuri= "urn:schemas-microsoft-com:office:smarttags "
name= "PlaceName "/ >
<o:SmartTagType namespaceuri= "urn:schemas-microsoft-com:office:smarttags "
name= "place "/ >
<!--[if !mso] >
<style >
st1\:*{behavior:url(#default#ieooui) }
</style >
<![endif]-- >
<style >
<!--
/* Font Definitions */
@(protected)
 {font-family:Tahoma;
 panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
 {margin:0in;
 margin-bottom:.0001pt;
 font-size:12.0pt;
 font-family: "Times New Roman ";}
a:link, span.MsoHyperlink
 {color:blue;
 text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
 {color:purple;
 text-decoration:underline;}
p
 {mso-margin-top-alt:auto;
 margin-right:0in;
 mso-margin-bottom-alt:auto;
 margin-left:0in;
 font-size:12.0pt;
 font-family: "Times New Roman ";}
span.EmailStyle18
 {mso-style-type:personal-reply;
 font-family:Arial;
 color:navy;}
@(protected) Section1
 {size:8.5in 11.0in;
 margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
 {page:Section1;}
/* List Definitions */
@(protected) l0
 {mso-list-id:676811598;
 mso-list-type:hybrid;
 mso-list-template-ids:112650656 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@(protected) l0:level1
 {mso-level-text: "%1\) ";
 mso-level-tab-stop:.5in;
 mso-level-number-position:left;
 text-indent:-.25in;}
ol
 {margin-bottom:0in;}
ul
 {margin-bottom:0in;}
-- >
</style >

</head >

<body lang=EN-US link=blue vlink=purple >

<div class=Section1 >

 I’ll wager that what is happening is
the following: <o:p > </o:p > 

 <o:p > </o:p > 

 <![if !supportLists] > 1) <![endif] > Your long SELECT starts and the system notes that the system change
number (SCN) is x <o:p > </o:p > 

 <![if !supportLists] > 2) <![endif] > 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) <o:p > </o:p > 

 <![if !supportLists] > 3) <![endif] > 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. <o:p > </o:p > 

 <![if !supportLists] > 4) <![endif] > 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 (See ORA-01555.ora-code.com) error. <o:p > </o:p > 

 <o:p > </o:p > 

 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. <o:p > </o:p > 

 <o:p > </o:p > 

<div >

 <st1:place w:st= "on " > <st1:PlaceName w:st= "on " > Justin </st1:PlaceName > <st1:PlaceName
w:st= "on " >Cave </st1:PlaceName > </st1:place > <o:p > </o:p > 

 Distributed Database Consulting, Inc. <o:p > </o:p > 

 <a href= "http://www.ddbcinc.com/askDDBC " >http://www.ddbcinc.com/askDDBC </a > <o:p > </o:p > 

 <o:p > </o:p > 

</div >

<div >

<div class=MsoNormal align=center style= 'text-align:center ' > 

<hr size=2 width= "100% " align=center tabindex=-1 >

 </div >

 From: 
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of NGUYEN Philippe (Cetelem) 
 Sent: Thursday, March 11, 2004
1:46 AM 
 To: 'oracle-l@(protected) ' 
 Subject: Strange snapshot too old
during a select statement 
 Importance: High <o:p > </o:p > 

</div >

 <o:p > </o:p > 

 Hi list! 
 
 we encounter a very strange
"ORA-01555 (See ORA-01555.ora-code.com) : 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 ? <o:p > </o:p > 

 TIA 
 
 Philippe <o:p > </o:p > 

</div >

</body >

</html >