Purpose of the script
Sample SQL script to show the following information.
- Who is currently logged on.
- If they are active, what SQL they are currently executing.
- 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