Re: UNTO TBS behavior in 9i

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 ?

• eg if you could re-write the SELECT statement to fetch only about 10% of the rows and then see how many Undo Records and Undo Blocks does the V$TRANSACTION entry show ?

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