"snapshot too old " and undo

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

"snapshot too old " and undo_retention

"snapshot too old " and undo_retention

2005-09-30 - By Hallas, John, Tech Dev

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

If it is a huge table then it does not really matter how accurate the
statistics are so why bother gathering them. Estimate them instead.

dbms_stats.set_table_stats( user, 'TABLE', numrows => 50000, numblks
=> 10000 );

dbms_stats.set_column_stats( user, 'TABLE', 'COLUMN', distcnt =>
15000 );

I think space is the only issue with a large undo_rention area. However
do you really want a job to be running for 36 hours

Is the table not partitioned? If so then use the partition option of
dbms_stats and break the job down into smaller sections

HTH

John

__ __

From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Roger Xu
Sent: 29 September 2005 22:33
To: Oracle-L@(protected) Org (E-mail)
Subject: "snapshot too old" and undo_retention

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)

<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="http:
//www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">

<style>

</style>

</head>

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

<div class=Section1>

If it is a huge
table then it does not really matter how accurate the statistics are so why
bother gathering them. Estimate them instead.<o:p></o:p>

  dbms_stats.set_table_stats(
user, 'TABLE', numrows => 50000, numblks => 10000 );<o:p></o:p>

  dbms_stats.set_column_stats( user, 'TABLE', 'COLUMN
', 
distcnt => 15000 );<o:p></o:p>

<o:p> </o:p>

I think space is the only issue with a large undo_rention
area. However do you really want a job to be running for 36 hours<o:p></o:p>

<o:p> </o:p>

Is the table not partitioned? If so then use the partition
option of dbms_stats and break the job down into smaller sections<o:p></o:p>

<o:p> </o:p>

HTH<o:p></o:p>

<o:p> </o:p>

John<o:p></o:p>

<o:p> </o
:p>

<o:p> </o
:p>

<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 Roger Xu 
Sent: 29 September 2005 22:33 
To: Oracle-L@(protected) Org
(E-mail) 
Subject: "snapshot too
old" and undo_retention<o:p></o:p>

</div>

<o:p> </o:p>

<div>

Hi
List,<o:p></o:p>

Background:
Oracle 9.2.0 w/ Auto Undo Management<o:p></o:p>

We
have a huge table and it takes more than 24 hours to collect its statistics.<o:p></o:p>

But
the undo_retention is set to 864000 i.e. 24 hours. As a result we see the
following a lot.<o:p></o:p>

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

Since
we have lots of space in the undo tablespace, I am thinking of increasing
undo_retention to 36 hours<o:p></o:p>

via
"ALTER SYSTEM SET
UNDO_RETENTION = 129600;"<o:p></o:p>

Is
this my only choice?<o:p></o:p>

What are
the drawbacks to have a large undo_retention value assuming we never run out
undo tablespace?<o:p></o:p>

 <o
:p></o:p>

Thanks,<o:p></o:p>

</div>

Roger
Xu <o:p></o:p>

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

</div>

</body>

</html>