Re: Query to ID running DDL

Buck

By querying the command column of the v$session table you can tell which sessions are doing what. If this column shows a 9, under 8i, then the user is creating an index. This will let you spot which user sessions are the ones running the 3rd-party scripts. You then have 3 options to discover the actual code:
1. Use SQL_TRACE and TKPROF, you will have to wait until the next time the query is run.
2. Use log miner to delve into the redo log that was active at the time the query was being run. See "Oracle8i Supplied PL/SQL Packages Reference" for more info.
3. If you have auditing turned on you can do a join between v$session and one of the audit tables, sorry can't remember which, to get the DDL.

Regards

Fraser McCallum
MVP Oracle
www.brainbench.com

"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message news:f98999c8.0109210946.2daf58aa_at_posting.google.com...
> Is there a table against which I can query to find currently running
> DDL? I have some 3rd-party scripts that are creating massive indexes.
> However, I can't really tell which step it is on.
>
> I tried V$SQLAREA, V$SESSION_LONGOPS, but didn't find anything usable.
> I see ALTER TABLE" statements in V$SQLAREA, but no CREATE INDEX
> statements.
>
Received on Sat Sep 22 2001 - 07:46:56 CDT