The Geek Diary

Oracle SQL script to Show current Users and SQL being Executed

by

Purpose of the script

Sample SQL script to show the following information.

  1. Who is currently logged on.
  2. If they are active, what SQL they are currently executing.
  3. How long they have been executing it. (last_call_et)

The script

Below is the SQL script:

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

set serveroutput on size 1000000
declare
    x number;
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, LAST_CALL_ET
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                               sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        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||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                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||action||client_info is not null;

Sample output from above SQL script

Below is the sample output from the above sql script.

SQL> @showsql

USERNAME               SID    SERIAL# PROCESS   STATUS
--------------- ---------- ---------- --------- ----------
SYS                      8       1011 5128      ACTIVE
SYSTEM                   9        101 3148      INACTIVE
SCOTT                   12       1962 5405      INACTIVE
--------------------
SYS(8,1011) ospid = 5128 program = sqlplus@stsun7 (TNS V1-V3)
Tuesday   10:03  Tuesday   10:15 last et = 0
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_

USERNAME        MODULE          ACTION          CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SYSTEM(9,101)   SQL*Plus
SCOTT(12,1962)  SQL*Plus

You May Also Like