RE: identify CTAS from v$ tables
- From: Joseph Amalraj <joseph@xxxxxxxxxxxxxx>
- To: ramick <ramick@xxxxxxxxxxx>
- Date: Tue, 25 Apr 2006 15:59:51 -0700 (PDT)
In this case, dictionary managed tablespaces are being used, but the extents
are properly sized. CTAS and drop table are the only sql that need space
management. I have suggested, to move towards delete/insert and use of global
temporary table where possible.
Most of the scripts use CTAS and run once in 30 minutes or 1 hour.
Any suggestions are welcome.
Regards
Joseph
ramick <ramick@xxxxxxxxxxx> wrote:
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
Apologies if I missed something, but are you sure the ST enqueue locks
are the result of CTAS?
They could be anything that requires the allocation or deallocation of
extents for any space management that is dictionary-managed.? Could be
?tablespaces of type temporary or permanent tablespaces.
---------------------------------
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Joseph Amalraj
Sent: Tuesday, April 25, 2006 2:15 PM
To: Stephane Faroult
Cc: ganstadba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: identify CTAS from v$ tables
In this database, there are 'ST' enqueue locks and i have to identify the
SQL statements issued by blocking and blocked sessions. I cannot use DDL
triggers.
Thanks
Joseph
Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:
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é°¨ane 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 > 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 /* 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
>
>
Other related posts:
