Killing session :- INACTIVE and MACHINE
set lines 300 pages 300 ;
col module for a40 ;
col machine for a10 ;
select sid , machine ,SQL_ID, module ,username , to_char(logon_time,'DD-MON-YYYY HH24:mi:ss') , last_call_et/60 from v$session where username is not null and status='INACTIVE' and machine='PRDBIDB' order by machine, logon_time;
spool kolap.sql
select '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''';'
from v$session s, v$process p
where s.paddr=p.addr
and s.username is not null
and s.status='INACTIVE' and s.machine='PRDBIDB' and s.last_call_et/60 > 10;
spool off ;
@kolap.sql
Killing session: INACTIVE and MACHINE NOT LIKE
SQL> select 'alter system kill session '''|| sid || ','|| serial# || ''' immediate ;'
from v$session
where username is not null
and status='INACTIVE'
and logon_time < trunc(sysdate)
and logon_time > trunc(sysdate-1)
and last_call_et/60 > 60
and MACHINE not like 'siebmtsh@PRDSAOM%'
order by machine, logon_time;
Kill Session: SID
set pages 300 lines 300 ;
select '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''';'
from v$session s, v$process p
where s.username is not null and s.username <> 'SYS'
and s.sid=&sid
and s.paddr=p.addr
;
Kill Session: SNIPED
SQL>
select '!kill -9 '||p.spid||chr(10)||
'alter system kill session '||''''||s.sid||','||to_char(s.serial#)||''''||';'
from v$session s,v$process p
where s.username is not null
and s.status = 'SNIPED'
and s.paddr=p.addr;
Kill Session: SQL ID
set serveroutput off ;
set heading off ;
set pages 0 ;
spool ksqlid.sql
select distinct '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'
from v$session_wait w,v$session s,v$sql q,v$process p
where s.sql_id='&SQL_ID'
and w.sid=s.sid
and p.addr=s.paddr
and s.SQL_HASH_VALUE=q.HASH_VALUE
and s.username is not null;
spool off ;
Kill Session: with Time
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate ;'
from v$session s
where s.username like upper('%&USERNAME%')
and s.LAST_CALL_ET/60 > '&time_in_min'
and s.username is not null and s.username <> 'SYS'
and event='&event'
;
spool killt.sql
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate ;'
from v$session s
where username is not null and (program like '%Toad%' or module like '%SQL*Plus%' )
and s.LAST_CALL_ET/60 > '&time_in_min'
and Status='INACTIVE';
spool off ;
@killt.sql
Kill Session: Long Running
set serveroutput off ;
set heading off ;
set pages 0 ;
spool klong.sql
select distinct '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''';'
from v$session s,v$process p
where p.addr=s.paddr
and s.sid in (select l.sid
from gv$session_longops l, gv$session s
where
s.sid=l.sid
and s.serial#=l.serial#
and totalwork <> SOFAR)
;
spool off ;
Kill Session: Event
spool kill_n.log
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate ;'
from v$session s
where
s.event='library cache lock' ;
spool off ;
@kill_n.log
Kill Session :- Blocking
set pages 300 lines 300 ;
spool kb.sql
select '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate ;'
from v$session s, v$process p
where s.username is not null and s.username <> 'SYS'
and s.sid in ( select distinct blocking_session from v$session )
and s.paddr=p.addr
;
spool off ;
Kill Session: Event
set lines 300 pages 0
set serveroutput off ;
set heading off ;
spool ke.sql
select distinct '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate ;'
from v$session_wait w,v$session s,v$sql q,v$process p
where w.event like '%&event%'
and w.sid=s.sid
and p.addr=s.paddr
and s.SQL_HASH_VALUE=q.HASH_VALUE
and s.status='ACTIVE'
and last_call_et/60 > &time
and s.username is not null
and s.type<>'BACKGROUND';
spool off ;
set pages 3000 lines 300 ;
set heading off ;
set feedback off ;
set serveroutput off ;
spool kill_inct.txt;
select '!kill -9 '||p.spid||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''';'
from v$session s, v$process p
where s.status='INACTIVE'
and s.username is not null and s.username <> 'SYS'
and s.paddr=p.addr
;
spool off ;
