Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Wed, 13 Apr 2011 04:41:20 -0700 (PDT)
Message-ID: <81b1de59-466b-4971-b2b4-378f37e16c8b_at_32g2000vbe.googlegroups.com>
On Apr 12, 11:05 am, Donatello Settembrino <donatello.settembr..._at_gmail.com> wrote:
> A table created with insert / * + append * /, has the blocks of the
> table
> with the "ITL clean" but if there are indexes, the indexes will have
> the "dirty ITL, will be
> therefore necessary (delayed) block cleanout
> select / * + index (.....) * / from table;
That's a good point about the indexes that I forgot to mention, although I assumed in this case due to the data volume mentioned the load is actually done without indexes enabled / created.
Note however that there is a subtle point about the blocks generated by the direct-path insert: Although the lock bytes on the rows are not set and do not need to be cleaned out, the commit SCN of the ITL entry is not known at insert time therefore the database has to look up the commit SCN (obviously by cleaning out a single block) when encountering such blocks and therefore I think you can still get a ORA-01555 error even with table blocks loaded via direct-path insert if the transaction table slot in the undo segment header has been overridden in the meantime and Oracle can not determine the commit SCN otherwise.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Apr 13 2011 - 06:41:20 CDT