Links
• Home
• Oracle DBA Forum
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
"snapshot too old " and undo_retention

"snapshot too old " and undo_retention

2005-09-29       - By Roger Xu

Reply:     1     2     3     4     5     6     7     8     9     10     >>  

Hi List,

Background: Oracle 9.2.0 w/ Auto Undo Management

We have a huge table and it takes more than 24 hours to collect its statistics.

But the undo_retention is set to 864000 i.e. 24 hours. As a result we see the
following a lot.

"ORA-01555 (See ORA-01555.ora-code.com): snapshot too old: rollback segment number 11 with name "_SYSSMU11$"
too small"

Since we have lots of space in the undo tablespace, I am thinking of increasing
undo_retention to 36 hours

via "ALTER SYSTEM SET UNDO_RETENTION = 129600;"


Is this my only choice?

What are the drawbacks to have a large undo_retention value assuming we never
run out undo tablespace?



Thanks,

Roger Xu



This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential and/or privileged information. Any
review, dissemination, copying, printing or other use of this e-mail by persons
or entities other than the addressee is prohibited. If you have received this e
-mail in error, please contact the sender immediately and delete the material.
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ _____
This email has been scanned for all viruses by the MessageLabs Email Security
System. Any questions please call 972-721-8257 or email your request to tech
_support@(protected)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">


<META content="MSHTML 6.00.2900.2180" name=GENERATOR></HEAD>
<BODY>
<DIV>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>Hi
List,</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>Background: Oracle
9.2.0 w/ Auto Undo Management</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>We have a huge
table
and it takes more than 24 hours to collect its statistics.</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>But the
undo_retention is set to 864000 i.e. 24 hours. As a result we see the following
a lot.</FONT></SPAN></P>
<P><FONT face=Courier><FONT size=2><SPAN
class=269431921-29092005>"</SPAN>ORA-01555 (See ORA-01555.ora-code.com): snapshot too old: rollback segment
number 11 with name "_SYSSMU11$" too small<SPAN
class=269431921-29092005>"</SPAN></FONT></FONT></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>Since we have lots
of space in the undo tablespace, I am thinking of increasing undo_retention to
36 hours</SPAN></FONT></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>via <FONT
style="BACKGROUND-COLOR: #ffff66">"</FONT></FONT><FONT face=Courier><FONT
size=2><FONT style="BACKGROUND-COLOR: #ffff66">ALTER </FONT>SYSTEM SET
UNDO_RETENTION&nbsp;= 129600;"<BR></FONT></FONT></SPAN></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>Is this my only
choice?</SPAN></FONT></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>What&nbsp;are the
drawbacks to have a large undo_retention value assuming we never run out undo
tablespace?</SPAN></FONT></P>
<P><FONT face=Courier size=2><SPAN
class=269431921-29092005></SPAN></FONT>&nbsp;</P>
<P><FONT face=Courier size=2><SPAN
class=269431921-29092005>Thanks,</SPAN></FONT></P></DIV>
<P><FONT face=Courier><FONT size=2>Roger Xu <BR></FONT></FONT></P>
<BR>
This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential and/or privileged information. Any
review, dissemination, copying, printing or other use of this e-mail by persons
or entities other than the addressee is prohibited. If you have received this e
-mail in error, please contact the sender immediately and delete the material.
<BR>
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ _____<BR>
This email has been scanned for all viruses by the MessageLabs Email Security
System. Any questions please call 972-721-8257 or email your request to tech
_support@(protected)<BR>
</BODY></HTML>