Re: Delayed block cleanout causing ora-1555?

On Mar 1, 2:06 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> Chuck schreef:> Oracle 9.2.0.7
>
> > Is there any way determine authoritatively if an ORA-1555 "snapshot too
> > old" error was caused by delayed block cleanout? I suspect it is
> > happening on overnight processing but am not 100% sure. The query starts
> > at 2am and failed at 3:30. Undo retention is 7 hours so that's not the
> > problem, and a query of v$undostat for that time period shows only about
> > 1g of undo being written during that time. The undo tablespace is 4g.
>
> > TIA
>
> There was a recent discussion where an ora-01555 occurred during
> an export - similar undo retention and undostats. One comment
> was that in 9i, undo retention is not guaranteed.
>
> Another option is an other session, that uses commits in a loop,
> during an update.
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

Chuck, do you have enough undo allocated to retain 7 hours of undo data? How much undo was being generated during this time period?

You can query v$undostat for the answers to my questions.

HTH -- Mark D Powell -- Received on Thu Mar 01 2007 - 18:33:04 CST