Re: Delayed block cleanout causing ora-1555?

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sat, 03 Mar 2007 21:19:43 +0000
Message-ID: <2YGdneZyZ-wGf3TYRVnyjgA@pipex.net>

Frank van Bortel 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.

It was my comment, and yes that's correct

> Another option is an other session, that uses commits in a loop, > during an update.

or indeed the overnight batch process itself committing frequently in a loop.

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info/services

Received on Sat Mar 03 2007 - 15:19:43 CST