Re: Preventing delayed block cleanout due to loading a datawarehouse

From: Jaap W. van Dijk <>
Date: Sat, 16 Apr 2011 19:43:23 GMT
Message-ID: <>

On Fri, 08 Apr 2011 21:02:28 GMT, (Jaap W. van Dijk) wrote:

>I'm in charge of a datawarehouse which we update every weekend. That
>means loading lots of tables with
>for every table. The volume of the total load is about half to one TB.
>Afterwards, when the tables are queried, every now and again an
>ORA-01555 happens, sometimes even after days. In my opinion this can
>only be because of delayed block cleanout, because loading and then
>querying a table is *always* done serially.
>If I would load the tables with
>would the blocks be written pre-cleaned out, thus preventing the
>ORA-01555 from happening?

Hi guys,

What an in-depth discussion! Thank you all for increasing my knowledge. As far as I know this stuff is not easily found, not even at the Oracle support site. I make good use of it.

On thing is still puzzling me: to prevent an ORA-01555 due to delayed block cleanout, in case of the rollback transaction slot being overwritten, in Note 40689.1 on the Oracle Support site the following is recommended:

alter session set optimizer_goal = rule; select count(*) from table_name;

for the same reason for which Randolf Geist recommends gathering statistics I guess, forcing a visit and cleaning out all the blocks. But why won't this run into the same ORA-01555? Or is discovering that the rollback transaction slot isn't there anymore somehow enough when doing a count(*) or when gathering statistics?

Jaap Received on Sat Apr 16 2011 - 14:43:23 CDT

Original text of this message