Re: Troubleshooting ORA-01555 while using AUM

Jonathan Lewis wrote:

> The most significant bit of the results is this:
>
> BEGIN END MAXQUERYLEN UNXPSTEALCNT EXPSTEALCNT
> SSOLDERRCNT NOSPACEERRCNT
> ----------------- ----------------- ----------- ------------ ----------- ---
> 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
>
> You have 9.2.0.4, retention time of 7200 seconds,
> and loads of free space in the undo tablespace.
>
> This output says that in the 20 minutes leading up to
> the two snapshot too old errors, your longest running
> queries were a couple of hundred seconds. If you had
> been running a query of 7200 second, then a load of the
> preceding rows from v$undostat would have shown the
> maxquerylen gradually increasing towards 7,200.
>
> So you seem to be getting snapshot too old when it
> should not be happening.
>
> Are you using LOBs, and is it possible that the failures
> are due to LOB chunk overwrites. I haven't checked
> whether the LOB error is reported at the top level as
> 1555, or as the underlying 22924 - this could be a
> cause of confusion. I also don't know if a LOB snapshot
> too old would appear in v$undostat (I doubt it, as it's
> not caused by destruction of undo).

Good point. It is common for those reporting problems to report a single ORA error message. Perhaps the OP had a stack and only gave us the first one. There might be valuable information elsewhere in the calling and error stacks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)