Re: Currently executing SQL statment

From: Dave Harris <dharris_at_uk.oracle.com>
Date: 1995/08/31
Message-ID: <4244sb$lrn_at_inet-nntp-gw-1.us.oracle.com>#1/1

posborn_at_ix.netcom.com (Paul Osborn) wrote:
>
> To: all
>
> Does anyone have the SQL code that will retrieve the currently
> executing SQL statment (I hope that makes sense). Some vendors of DB
> monitoring tools (Adhawk & DBVision) are demonstrating the ability to
> extract and display the currently executing SQL statement. My guess is
> that they are getting it from a V$ table in 7.1+.
>
> Thanks in advance,
>
> Paul Osborn
> posborn_at_ix.netcom.com
> Menlo Software
> (415) 324-1286

although i haven't tried this the tables you need to look at are: v$session and v$sqltext

v$session has the 2 columns sql_address and sql_hash_value which will identify the sql statement currently or last executed(?) for a specified session.

v$sqltext contains the sql text. presumably the sql would look something like:

select vt.sql_text
from v$sqltext vt,

       v$session vs
where  vs.audsid     = :session_id

and vt.address = vs.sql_address
and vt.hash_value = vs.sql_hash_value order by vt.piece;

as noted, i haven't tried this.
hope this helps

dave harris Received on Thu Aug 31 1995 - 00:00:00 CEST