Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: UNTO TBS behavior in 9i

Re: UNTO TBS behavior in 9i

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 25 Jan 2007 20:52:05 +0800
Message-Id: <7.0.1.0.0.20070125204701.01b33008@singnet.com.sg>

As Daniel has pointed out, if you have any ON INSERT Triggers on the target table, those triggers
would also be generating Undo.

Have you been able to monitor the undo generation by querying V$TRANSACTION ?

Finally, why not use APPEND hint to generate Direct Path loading ? alter table target_table nologging ;

   insert /*+ APPEND */ into target_table SELECT .. < or try with only a subset of rows>

   <finally> commit ; alter table target_table logging;

If you are really just trying to investigate the perplexing behaviour, you shouldn't really
need to do a 5.5GB insert and wait to hit 30GB Undo. Just try your insert with a 10% subset
but monitor the operation in V$UNDOSTAT {assuming that no other transactions are running
at the same time} or, better, V$TRANSACTION -- which you can sample every second !

Hemant

At 01:19 AM Thursday, Ram Raman wrote:
>Thanks for your replies.
>
>1. We are not doing multiple commits. Just one INSERT INTO SELECT
>statement with one commit at the end. There were no other
>transactions when we started this one.
>
>2. The space for both data and index is about 5.5Gb.
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantoracledba.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 06:52:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US