Re: Currently executing SQL statment

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/08/31
Message-ID: <424ce5$mih_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

Here is what I use:

  • showsql.sql =========================== select username, sid, serial#, status from v$session where username is not null /

set heading off
set termout off
column sql_text format a55 word_wrapped
column username format a20
spool .xtmpx.sql

select 'select '''||username||'('||sid||','||serial#||
       ')'' username, sql_text ' ||
       'from v$sqlarea where address = hextoraw( ''' ||
        rawtohex(sql_address) || ''' );' sql_text
from v$session where username is not null   and rawtohex( sql_address ) <> '00'
/
spool off
set heading on
set termout on
_at_.xtmpx.sql
====================== eof =============================

I am using sql to write sql cause joining the v$sqlarea table can be *very* slow if your shared sql area is large. This shows you who is logged on and if they are executing SQL, will show the sql that was being executed when you started the script.

You need to have write privelege in the current working directory or change my spool command to some other place.

Hope this helps..

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Aug 31 1995 - 00:00:00 CEST

Original text of this message