Re: truncate /*+append*/

On Sat, 04 Mar 2006 16:49:42 -0800, cpiodumpv wrote:

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

It does, but you cannot see it, as it is no longer active when it finishes. DDL statements do implicit commit, so you can't ever see it as active after it's finished. The problem with my query is that it shows only the current transactions, ones pointed to by sessions in V$SESSION table. If the transaction is over, and the session that issued it now points to another transaction, you will see nothing on the output of the query. Also, you cannot see any information about finished transactions as you don't have V$SESSION history. The only way to observe the finished transactions is auditing and it will not tell you how many undo blocks did the transaction consume. As of 10g you can catch SQL and bind variable values, even without DBMS_FGA, but you can't see how many undo blocks did it consume at any given point in time.

My query is intended for monitoring transaction rollback and undo consumption. If you want to study the execution path of DDL statements, enable the event 10046, level 12 and execute your DDL with tracing enabled.

-- 
http://www.mgogala.com