Re: identify CTAS from v$ tables

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: joseph@xxxxxxxxxxxxxx
  • Date: Tue, 25 Apr 2006 23:21:52 +0200

Joseph,

Would a DDL trigger be an acceptable solution to you? I haven't tried it recently, but I *think* that you can catch the statement text in the trigger. I am not fully certain about that, because since DDL statements are nothing else (ultimately) than DML statements applied to dictionary tables some are broken into their constituent DML components so fast that they let no track in the SGA.
Otherwise perhaps that V$object_dependency could help too ...

HTH

Stéphane Faroult

Joseph Amalraj wrote:

After going thru' the v$fixed_view_definition for v$sql, v$open_cursor and
v$sqltext, I could only get the name of the table being created by the following query.
sys@clfypurg <mailto:sys@clfypurg>> select kglnaobj from x$kgllk where kgllkmod = 3;
KGLNAOBJ
------------------------------------------------------------
MY_LARGE_TABLE

if "alter session set sql_trace = true" is run before executing CTAS the trace
does generate the CTAS sql statement.
Can the sql be found from any x$ table ??
Thanks
Joseph

*/Michael McMullen <ganstadba@xxxxxxxxxxx>/* wrote:

    Here's what TOAD uses but as I recall you can't get the underlying
    DDL statement for CTAS. Maybe changed in 10g.
    SELECT   sql_text
        FROM v$sqltext_with_newlines
       WHERE hash_value = TO_NUMBER (:HASH)
    ORDER BY piece




--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2006