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