Tuesday, 8 March 2011

Killing the Inactive forms sessions running for more then 12 hrs

-- Session Killing script--be carefull

set pages 200
select 'alter system kill session '''||sid||','||serial#||''';'
from
(
select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,
trunc( sysdate-b.logon_time ) "Dy",
trunc( mod( (sysdate-b.logon_time)*24, 24 ) )  "Hr",
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) )  "Mi",
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"
from V$access a,v$session b, v$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status='INACTIVE'
and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like
'SQL%' or b.program like '%sql%' or b.program like '%FRM%'
or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1));

No comments:

Post a Comment