Preventing delayed block cleanout due to loading a datawarehouse

From: Jaap W. van Dijk <>
Date: Fri, 08 Apr 2011 21:02:28 GMT
Message-ID: <>


I'm in charge of a datawarehouse which we update every weekend. That means loading lots of tables with

INSERT /*+ APPEND PARALLEL */ ... SELECT ... FROM ... COMMIT 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

CREATE TABLE ... AS SELECT ... FROM ... would the blocks be written pre-cleaned out, thus preventing the ORA-01555 from happening?

Jaap. Received on Fri Apr 08 2011 - 16:02:28 CDT

Original text of this message