Re: Currently executing SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 20 Jun 1999 13:54:58 GMT
Message-ID: <376ff285.2120909@newshost.us.oracle.com>

A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com> (if that email address didn't require changing) On Sat, 19 Jun 1999 21:48:52 -0400, you wrote:

>Does anyone have a quick way to find what SQL a user is currently
>executing?
>I've been a little spoiled by tools lately.
>
>- Dc.

This is the sqlplus script I like to use:

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

begin

    for x in
    ( select username||'('||sid||','||serial#||') ospid = ' || process ||

                       ' program = ' || program username,
     to_char(LOGON_TIME,' Day HH24:MI') logon_time,
     to_char(sysdate,' Day HH24:MI') current_time,
             sql_address
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null ) loop
        for y in ( select sql_text
                     from v$sqlarea
                    where address = x.sql_address ) loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' || x.current_time);
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;

    end loop;
end;
/

column username format a15 word_wrapped column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,

       module,
       action,
       client_info

from v$session
where module is not null;

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 20 1999 - 08:54:58 CDT