Re: truncate /*+append*/

You read my mind..... My next question was where do I find the amount of undo generated by a transaction. This query is very helpful thank you.

Allow me to check my understanding thus far. There is undo generated to protect the data dictionary i.e moving the high water mark or altering the data structure of an index. This undo in turn generates it's own redo; thus, the only reason there is redo generated is to protect the undo. Correct?

In the below scenario why does the "create table as" statement not generate undo?

Thanks in advance.

chotaboy_at_test>drop table t;

Table dropped.

chotaboy_at_test>create table t nologging as select * from all_objects;

Table created.

chotaboy_at_test>host cat get_undo.sql
column username format a10

select sess.username,
  t.used_ublk "Undo blocks",
  t.status "Trans. Status"
FROM v$session sess,
  v$transaction t,
  v$process p
WHERE sess.saddr = t.ses_addr
 AND sess.paddr = p.addr
ORDER BY t.used_ublk DESC
/

chotaboy_at_test>@get_undo

no rows selected

chotaboy_at_test>alter table t nologging;

Table altered.

chotaboy_at_test>truncate table t;

Table truncated.

chotaboy_at_test>insert into t select * from all_objects;

11632 rows created.

chotaboy_at_test>@get_undo

USERNAME Undo blocks Trans. Status

---------- ----------- ------------------------------------------------
CHOTABOY             6 ACTIVE

chotaboy_at_test> Received on Sat Mar 04 2006 - 18:49:42 CST