Re: Troubleshooting ORA-01555 while using AUM

Pls. see below:

"Tech Geek" <Tech_Geek_at_Gawab-IHateSpam.com> wrote in message news:iEv0c.19155$6K.16090_at_nwrddc02.gnilink.net...
> Hi VC,
>
> Comments in-line.
>
> "VC" <boston103_at_hotmail.com> wrote in message
> news:mur0c.87693$4o.109763_at_attbi_s52...
> > Hello,
> >
> > "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)
> > >
> > > My understanding after going through the oracle documentation and
other
> > > white papers, documents on the subject, I understand that the values
in
> > the
> > > column v$undostat. UNXPSTEALCNT would be incremented by 1, whenever
an
> > > attempt is made to obtain undo space for new transaction by stealing
the
> > > unexpired extents from other transactions (committed or inactive).
> Also,
> > > the values in the column v$undostat.nospaceerrcnt would be incremented
> by
> > 1,
> > > whenever a space request was made and there was no free space.
> > >
> > > Given this understanding, whenever, I hit ORA-1555, I should see
> non-zeros
> > > in any of the two columns. I do see the value equal to the number of
> > > ORA-1555s I encounted in the column v$undostat.SSOLDERRCNT, but am I
> > always
> > > seeing non-zeros values in unxpstealcnt and nospacerrcnt columns.
This
> is
> > > making troubleshooting ORA-1555s tough.
> >
> > I am probably missing something since, judging by your description, you
> see
> > a consistent picture.
> >
> > Here's what I think was happening:
> >
> > The undo segment ran out of space _and_ the undo tablespace did not have
> any
> > more space to allow for the segment growth . Oracle went possibly
through
> > these stages:
> >
> > 1. It tried to acquire some undo space from unused(expired) extents
from
> > another segment (the UNXPSTEALCNT was incremented);
>
> -- Here is the difficult part, values for almost all of columns
especially -
> UNXPSTEALCNT, EXPSTEALCNT
> are zeros.

OK, I see now... Your original message was confusing because you wrote: "but am I always
seeing non-zeros values in unxpstealcnt and nospacerrcnt columns"

If the values are zeroes and you are experiencing the 1555 error, then the above columns in v$undostat view are indeed useless.

Could you supply more information about under exactly what circumstances the 1555 is happening in your case ?

VC Received on Sun Feb 29 2004 - 19:29:52 CST