Friday, June 14, 2013

Oracle: get info about active running sessions with their queries


select t.sql_text, t.disk_reads,t.rows_processed,
t.elapsed_time,t.runtime_mem, t.executions, t.users_executing,
t.cpu_time, t.first_load_time, t.last_active_time, s.osuser, s.machine, s.status, to_char( logon_time, 'Mon dd@hh24:mi') logon_time
, rtrim (s.module)||decode( nvl(length( rtrim(s.module)),0),0,'',' ')|| upper(s.program) running_from,
'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' AS command_to_kill,
'-- This will kill ' || s.schemaname || ' session, is occupied by ' || s.osuser || ', ''' || s.program
|| ''', started from '''|| machine || ''' at ' || first_load_time as notes
from v$session s, v$process p, v$sql t
where ( p.addr = s.paddr ) and s.type!='BACKGROUND'
and upper(s.program) not like '%CJQ0%' and s.program is not null and s.username is not null
and t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.username <> 'SYSTEM'
and s.SID NOT IN (SELECT sys_context('USERENV','SID') FROM dual)
--and s.status = 'ACTIVE'
--and t.parsing_schema_name = '<SCHEMA_NAME>'
order by s.username;