Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

not all DDL in V$SQL

170 views
Skip to first unread message

ErikYkema

unread,
Oct 3, 2008, 11:12:31 AM10/3/08
to
Hello,

(oracle 9.2.0.8 on AIX5/3)

I seem to be missing many SQL statements that recently executed
(seconds) in V$SQL and/or V$SQLAREA.
These are mostly CTAS (create table as select x, y, z, from table
ab).
Also the SELECT part of the CTAS is not visible as a separate
statement.

I see other DDL like truncate, create bitmap index, however no drop.

Does anyone explain and comprehend this behaviour?

Regards, Erik

DA Morgan

unread,
Oct 3, 2008, 4:17:18 PM10/3/08
to

Go to Morgan's Library at www.psoug.org and look up
DDL Event Triggers. Then look at the code related to:
"Trigger To Log Multiple DDL Activities"
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

ErikYkema

unread,
Oct 4, 2008, 9:36:15 AM10/4/08
to
On 3 okt, 22:17, DA Morgan <damor...@psoug.org> wrote:
> ErikYkema wrote:
> > Hello,
>
> > (oracle 9.2.0.8 on AIX5/3)
>
> > I seem to be missing many SQL statements that recently executed
> > (seconds) in V$SQL and/or V$SQLAREA.
> > These are mostly CTAS (create table as select x, y, z, from table
> > ab).
> > Also the SELECT part of the CTAS is not visible as a separate
> > statement.
>
> > I see other DDL like truncate, create bitmap index, however no drop.
>
> > Does anyone explain and comprehend this behaviour?
>
> > Regards, Erik
>
> Go to Morgan's Library atwww.psoug.organd look up

> DDL Event Triggers. Then look at the code related to:
> "Trigger To Log Multiple DDL Activities"
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

Thanks Daniel,
Looks very useful.
However I want to do performance analysis and IO monitoring on these
kinda SQL statements and would be helped if I found the CTAS DDL in
these kinda views.
Any clues why these aren't captured there?

Regards, Erik

DA Morgan

unread,
Oct 4, 2008, 5:24:46 PM10/4/08
to
ErikYkema wrote:

> Thanks Daniel,
> Looks very useful.
> However I want to do performance analysis and IO monitoring on these
> kinda SQL statements and would be helped if I found the CTAS DDL in
> these kinda views.
> Any clues why these aren't captured there?
>
> Regards, Erik

Overhead. What you propose, CTAS, would create substantial overhead.


--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington

damo...@x.washington.edu (replace x with u to respond)

0 new messages