killing session in Oracle - Queries

 

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 ;