set echo off set verify off set lines 1000 set pages 999 set head on set feed on set serveroutput on size 1000000 format wrap set trimspool on set termout on set long 10000 spool sess.rpt prompt accept x_username prompt 'Username : ' accept x_program prompt 'Program : ' accept x_active prompt 'Active only ? ' accept x_sid prompt 'SID : ' accept x_has_user prompt 'Non-null users only [Y]: ' default 'Y' declare lt_username v$session.username%type := upper( '%' || '&&x_username' || '%' ); cursor csr_db is select 'Database : ' || name || ' ' || to_char( sysdate, 'DD MONTH HH24:MI' ) from v$database; s_db varchar2( 80 ); cursor csr_sess is select decode( b.instance_number, a.inst_id, '-->', ' ' ) "Current", a.* from gv$session a, v$instance b where ( a.username like lt_username or ( '&&x_username' is null and a.username is null ) ) and a.program like '%&&x_program%' and ( upper( nvl( '&&x_active', 'N' ) ) <> 'Y' or a.status = 'ACTIVE' ) and a.sid = nvl( '&&x_sid', sid ) and ( ( '&&x_has_user' = 'Y' and username is not null ) or ( nvl( '&&x_has_user', 'N' ) <> 'Y' ) ) order by a.username, a.terminal, a.program, a.logon_time; cursor c_process ( x_inst_id in gv$process.inst_id%type, x_addr in gv$process.addr%type ) is select * from gv$process where inst_id = x_inst_id and addr = x_addr; r_process gv$process%rowtype; li_counter integer := 0; li_killed integer := 0; li_sniped integer := 0; li_active integer := 0; li_inactive integer := 0; li_other integer := 0; begin open csr_db; fetch csr_db into s_db; close csr_db; dbms_output.put_line( 'Session details for ' || s_db || chr(10) ); dbms_output.put_line( ' Inst ID OsUser SID/Serial/Inst User Status Logon time Program Module Terminal Client Info Service Name OS Process ID SQL ID Action ' ); dbms_output.put_line( ' ------- ------ --------------- ---- ------ ---------- ------- ------ -------- ----------- ------------ ------------- ------ ------' ); for i in csr_sess loop li_counter := li_counter + 1; if i.status = 'KILLED' then li_killed := li_killed + 1; elsif i.status = 'SNIPED' then li_sniped := li_sniped + 1; elsif i.status = 'ACTIVE' then li_active := li_active + 1; elsif i.status = 'INACTIVE' then li_inactive := li_inactive + 1; else li_other := li_other + 1; end if; open c_process( i.inst_id, i.paddr ); fetch c_process into r_process; close c_process; dbms_output.put_line( i."Current" || to_char( i.inst_id, '999999' ) || ' ' || rpad( nvl( i.OSUSER, ' ' ), 9 ) || ' ' || '(' || rpad( to_char( i.SID ) || ',' || to_char( i.serial# ) || ',' || to_char( i.inst_id ) || ')', 16 ) || ' ' || rpad( nvl( i.username, ' ' ), 25 ) || ' ' || rpad( i.STATUS, 10 ) || ' ' || to_char( i.logon_time, 'DD MON HH24:MI' ) || ' ' || rpad( nvl( i.program, ' ' ), 30 ) || ' ' || rpad( nvl( i.module, ' ' ), 20 ) || ' ' || rpad( nvl( i.terminal, ' ' ), 10 ) || ' ' || rpad( nvl( i.client_info, ' ' ), 12 ) || ' ' || rpad( nvl( i.service_name, ' ' ), 20 ) || ' ' || to_char( r_process.spid, '999999999999' ) || ' ' || rpad( nvl( i.sql_id, ' ' ), 13 ) || ' ' || i.action ); end loop; dbms_output.put_line(chr(10)); dbms_output.put_line( 'No. of sessions : ' || li_counter ); dbms_output.put_line( 'No. active : ' || li_active ); dbms_output.put_line( 'No. inactive : ' || li_inactive ); dbms_output.put_line( 'No. killed : ' || li_killed ); dbms_output.put_line( 'No. sniped : ' || li_sniped ); dbms_output.put_line( 'Other : ' || li_other ); end; / spool off