Path: news.f.de.plusline.net!news-fra1.dfn.de!news-stu1.dfn.de!news.germany.com!multikabel.net!feed20.multikabel.net!newshub2.home.nl!newshub1.home.nl!home.nl!not-for-mail From: Frank van Bortel Newsgroups: comp.databases.oracle.server Subject: Re: Delayed block cleanout causing ora-1555? Date: Sun, 04 Mar 2007 10:41:24 +0100 Organization: @Home Benelux Lines: 38 Message-ID: References: <2YGdneZyZ-wGf3TYRVnyjgA@pipex.net> Reply-To: frank.van.bortel@gmail.com NNTP-Posting-Host: cc28855-a.hnglo1.ov.home.nl Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: news3.zwoll1.ov.home.nl 1173001204 18368 82.75.117.80 (4 Mar 2007 09:40:04 GMT) X-Complaints-To: usenet@corp.home.nl NNTP-Posting-Date: Sun, 4 Mar 2007 09:40:04 +0000 (UTC) User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) In-Reply-To: <2YGdneZyZ-wGf3TYRVnyjgA@pipex.net> X-Enigmail-Version: 0.94.1.2 Xref: news.f.de.plusline.net comp.databases.oracle.server:194938 Niall Litchfield schreef: > 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. > > > As Chuck mentions "the query starts at 2 am", I ruled out the option of updates, but maybe "query' is a more generalized term for Oracle processes of any type where he lives; English is not my native tongue, so every now and again, I misunderstand or misinterpret things. -- Regards, Frank van Bortel Top-posting is one way to shut me up...