Re: Troubleshooting ORA-01555 while using AUM

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c1usqq$j3p$1_at_titan.btinternet.com...
>
> To summarise this post and the followups:
> I think you have said you have:
>
> undo_retention = 2 hours
>
> 13 GB of undo tablespace, but 8GB
> of that are reported in dba_free_space
>
> an export that can fail with
> "snapshot too old" after only 40 minutes.
>
> v$undostat shows:
> Zero for 'stolen unexpired'
> Zero for 'space errors'.
> non-zero for 'snapshot too old'.
>
>
> It does look as if something is going
> wrong somewhere. What is the maximum
> value in the column maxquerylen ? Does
> the column expstealcnt give you any clues
> about extents moving 'legally' from one
> segment to another ?

Jonathan,

Thanks a lot for wonderful summary. Here is the extract from v$undostat, I am including only those which are more relevant to the problem. I included about 5 lines before the problem and after the problem. I am sure, that the following extract information will not show nicely formatted,if it is so, please check the values in the column five, which denotes values for the column SSOLDERRCNT (it has non-zero values of 2, 1, 4).

BEGIN END MAXQUERYLEN UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT

----------------- ----------------- ----------- ------------ ----------- ---
-------- -------------
27-02-04 16:05:40 27-02-04 16:15:40         434            0           0
0             0
27-02-04 15:55:40 27-02-04 16:05:40         113            0           0
0             0
27-02-04 15:45:40 27-02-04 15:55:40         193            0           0
2             0
27-02-04 15:35:40 27-02-04 15:45:40         206            0           0
0             0
27-02-04 15:25:40 27-02-04 15:35:40         102            0           0
0             0
27-02-04 15:15:40 27-02-04 15:25:40         199            0           0
0             0
26-02-04 02:05:40 26-02-04 02:15:40         563            0           0
0             0
26-02-04 01:55:40 26-02-04 02:05:40          77            0           0
0             0
26-02-04 01:45:40 26-02-04 01:55:40         176            0           0
1             0
26-02-04 01:35:40 26-02-04 01:45:40         158            0           0
0             0
26-02-04 01:25:40 26-02-04 01:35:40          78            0           0
0             0
26-02-04 01:15:40 26-02-04 01:25:40         166            0           0
0             0
26-02-04 01:05:40 26-02-04 01:15:40         190            0           0
0             0
26-02-04 00:55:40 26-02-04 01:05:40         115            0           0
4             0
23-02-04 14:55:40 23-02-04 15:05:40         137            0           0

Thanks for your help and time.
TG

>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> March 2004 Hotsos Symposium - The Burden of Proof
> Dynamic Sampling - an investigation
> March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Tech Geek" <Tech_Geek_at_Gawab-IHateSpam.com> wrote in message
> news:gno0c.10382$C65.9964_at_nwrddc01.gnilink.net...
> > Hi Gurus,
> >
> > I am trying to troubleshoot the dreaded ORA-01555 - Snapshot too old
> errors.
> > Here is the relevant info:
> >
> > OS : Sun Solaris 2.8
> > Oracle: Oracle9i Rel 2 (9.2.0.4)
> > undo_retention = 7200 (2 hours)
> >
>
>
Received on Mon Mar 01 2004 - 17:31:00 CST