Re: identify CTAS from v$ tables

From: Joseph Amalraj <joseph_at_amalrajinc.com>
Date: Tue, 25 Apr 2006 12:37:26 -0700 (PDT)
Message-ID: <20060425193726.44704.qmail@web408.biz.mail.mud.yahoo.com>

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_at_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_at_hotmail.com> 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   

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 25 2006 - 14:37:26 CDT