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-10-05       - By Roger Xu

Reply:     <<     11     12     13     14     15     16  

We are on SAP 4.6C. Which field in table DBSTATC is for sample size?

SQL> select * from sapr3.DBSTATC where DBOBJ = 'BSIS';

DBOBJ                DO OBJOW                DBTYP      VW A OBJEC
AEDAT
-- ---- ---- ---- -- -- -- ---- ---- ---- -- -- ---- -- -- - -- ---- --
-- -----
    SIGNI AMET OPTIO T H TDDAT    DURAT  P
-- ---- -- ---- -- -- - - -- ----- -- --- -
BSIS                 01 SAPR3                ORACLE     A  A D_FA
19990219
        2 C            X          000000

SQL> desc sapr3.DBSTATC
Name                                      Null?    Type
-- ---- ---- ---- ---- ---- ---- ---- --- -- -----
-- ---- ---- ---- ---- -----
DBOBJ                                     NOT NULL VARCHAR2(20)
DOTYP                                     NOT NULL VARCHAR2(2)
OBJOW                                     NOT NULL VARCHAR2(20)
DBTYP                                     NOT NULL VARCHAR2(10)
VWTYP                                     NOT NULL VARCHAR2(2)
ACTIV                                     NOT NULL VARCHAR2(1)
OBJEC                                     NOT NULL VARCHAR2(10)
AEDAT                                     NOT NULL VARCHAR2(8)
SIGNI                                     NOT NULL NUMBER(3)
AMETH                                     NOT NULL VARCHAR2(4)
OPTIO                                     NOT NULL VARCHAR2(5)
TOBDO                                     NOT NULL VARCHAR2(1)
HISTO                                     NOT NULL VARCHAR2(1)
TDDAT                                     NOT NULL VARCHAR2(8)
DURAT                                     NOT NULL VARCHAR2(6)
PLAND                                     NOT NULL VARCHAR2(1)


Thanks.

Roger

-- --Original Message-- --
From: Hand, Michael T [mailto:HANDM@(protected)]
Sent: Tuesday, October 04, 2005 10:54 AM
To: Roger Xu
Cc: Oracle-L@(protected) Org (E-mail)
Subject: RE: "snapshot too old" and undo_retention

Roger,
9.2.0.6    Tru64 5.1     SAP R/3 4.7
Our BSIS table is only about 1/4 the size of yours.  When we run
Brconnect the sample size is 3% (from the DBSTATC table) and no
histograms are collected.  Also, the table is stable/slow growing enough
so that stats haven't been refreshed in more than 6 months (and that was
due to a DMT to LMT conversion).

I realize each SAP install has its own unique conditions, but your
statistics gathering method on this table seems extreme.  Some
suggestions before considering partitions: 1) look into archiving as
Jared mentioned, 2) change Brconnect parameters to use dbms_stats
instead of analyze, 3) reduce the frequency that stats are collected
(the default is 50% change in # of rows), 5) validate need for
histograms & 100% sample size (change DBSTATC),  lastly, if 5) can not
be reduced, look into collecting stats on another box and import them.

Regards,
Mike

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Roger Xu
Sent: Friday, September 30, 2005 5:35 PM
To: Robyn
Cc: Oracle-L@(protected) Org (E-mail)
Subject: RE: "snapshot too old" and undo_retention

First of thanks to everyone who replied my email.
We use SAP tools (brconnect) to collect statistics and it comes out
"ANALYZE TABLE ..COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED COLUMNS
SIZE 1 FOR ALL INDEXES".
We do not have a data warehouse and the table BSIS has
273 million rows. We are on 9.2.0.4 and this table is not partitioned
and the table itself is 80 gig in size.
Anybody partition this table? How? Thanks.

--
This transmission is intended only for use by the addressee(s) named
herein and may contain information that is proprietary, confidential
and/or legally privileged. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or use
of the information contained herein (including any reliance thereon) is
STRICTLY PROHIBITED. If you received this transmission in error, please
immediately contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.



For technical support please email tech_support@(protected) or you can
call (972)721-8257.
This email has been scanned for all viruses by the MessageLabs Email
Security System.




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><TITLE></TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2180" name=GENERATOR></HEAD>
<BODY><!-- Converted from text/plain format -->
<P><FONT size=2>We are on SAP 4.6C. Which field in table DBSTATC is for sample
size?</FONT></P>
<P><FONT face=Courier color=#0000ff size=2>SQL&gt; select * from sapr3.DBSTATC
where DBOBJ = 'BSIS';</FONT></P>
<P><FONT face=Courier color=#0000ff
size=2>DBOBJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;
DO
OBJOW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
DBTYP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VW A OBJEC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AEDAT<BR>-- ---- ---- ---- -- -- -- ---- ---- ---- -- -- ---- -- -- - -- ------
-
-- -----<BR>&nbsp;&nbsp;&nbsp;&nbsp; SIGNI AMET OPTIO T H
TDDAT&nbsp;&nbsp;&nbsp; DURAT&nbsp; P<BR>-- ---- -- ---- -- -- - - -- -----
-- ---
-<BR>BSIS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
01
SAPR3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
ORACLE&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp; A
D_FA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
19990219<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 000000</FONT></P>
<P><FONT face=Arial color=#0000ff size=2><FONT face=Courier>SQL&gt; desc
sapr3.DBSTATC<BR>&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
Null?&nbsp;&nbsp;&nbsp; Type<BR>&nbsp;-- ---- ---- ---- ---- ---- ---- ---- ---
-- -----
-- ---- ---- ---- ---- -----<BR>&nbsp;DBOBJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;
NOT NULL
VARCHAR2(20)<BR>&nbsp;DOTYP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
NOT NULL
VARCHAR2(2)<BR>&nbsp;OBJOW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(20)<BR>&nbsp;DBTYP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
NOT NULL
VARCHAR2(10)<BR>&nbsp;VWTYP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
NOT NULL
VARCHAR2(2)<BR>&nbsp;ACTIV&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(1)<BR>&nbsp;OBJEC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(10)<BR>&nbsp;AEDAT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
NOT NULL
VARCHAR2(8)<BR>&nbsp;SIGNI&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
NUMBER(3)<BR>&nbsp;AMETH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(4)<BR>&nbsp;OPTIO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(5)<BR>&nbsp;TOBDO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(1)<BR>&nbsp;HISTO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(1)<BR>&nbsp;TDDAT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(8)<BR>&nbsp;DURAT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL
VARCHAR2(6)<BR>&nbsp;PLAND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
NOT NULL VARCHAR2(1)<BR></FONT></P></FONT>
<P><FONT size=2></FONT><FONT size=2>Thanks.</FONT></P>
<P><FONT size=2>Roger<BR><BR>-- --Original Message-- --<BR>From: Hand, Michael
T
[<A href="mailto:HANDM@(protected)">mailto:HANDM@(protected)</A>]<BR>Sent:
Tuesday, October 04, 2005 10:54 AM<BR>To: Roger Xu<BR>Cc: Oracle-L@(protected)
Org (E-mail)<BR>Subject: RE: "snapshot too old" and
undo_retention<BR><BR>Roger,<BR>9.2.0.6&nbsp;&nbsp;&nbsp; Tru64
5.1&nbsp;&nbsp;&nbsp;&nbsp; SAP R/3 4.7<BR>Our BSIS table is only about 1/4 the
size of yours.&nbsp; When we run Brconnect the sample size is 3% (from the
DBSTATC table) and no histograms are collected.&nbsp; Also, the table is
stable/slow growing enough so that stats haven't been refreshed in more than 6
months (and that was due to a DMT to LMT conversion).<BR><BR>I realize each SAP
install has its own unique conditions, but your statistics gathering method on
this table seems extreme.&nbsp; Some suggestions before considering partitions:
1) look into archiving as Jared mentioned, 2) change Brconnect parameters to
use
dbms_stats instead of analyze, 3) reduce the frequency that stats are collected
(the default is 50% change in # of rows), 5) validate need for histograms &amp;
100% sample size (change DBSTATC),&nbsp; lastly, if 5) can not be reduced, look
into collecting stats on another box and import
them.<BR><BR>Regards,<BR>Mike<BR><BR>-- --Original Message-- --<BR>From:
oracle-l-bounce@(protected)<BR>[<A
href="mailto:oracle-l-bounce@(protected)">mailto:oracle-l-bounce@(protected)
.org</A>]
On Behalf Of Roger Xu<BR>Sent: Friday, September 30, 2005 5:35 PM<BR>To:
Robyn<BR>Cc: Oracle-L@(protected) Org (E-mail)<BR>Subject: RE: "snapshot too old
"
and undo_retention<BR><BR>First of thanks to everyone who replied my
email.<BR>We use SAP tools (brconnect) to collect statistics and it comes out
"ANALYZE TABLE ..COMPUTE STATISTICS FOR TABLE&nbsp; FOR ALL INDEXED COLUMNS
SIZE
1 FOR ALL INDEXES".<BR>We do not have a data warehouse and the table BSIS
has<BR>273 million rows. We are on 9.2.0.4 and this table is not partitioned
and
the table itself is 80 gig in size.<BR>Anybody partition this table? How?
Thanks.<BR><BR>--<BR>This transmission is intended only for use by the
addressee(s) named herein and may contain information that is proprietary,
confidential and/or legally privileged. If you are not the intended recipient,
you are hereby notified that any disclosure, copying, distribution, or use of
the information contained herein (including any reliance thereon) is STRICTLY
PROHIBITED. If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety, whether in
electronic or hard copy format. Thank you.<BR><BR><BR><BR>For technical support
please email tech_support@(protected) or you can<BR>call (972)721-8257.<BR>This
email has been scanned for all viruses by the MessageLabs Email Security
System.<BR><BR></P></FONT>
<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>