Script for killing sessions in Oracle

Hi friends, I am sharing the script generating kill statements based on various parameters.
It is crucial for DBAs to have such scripts handy in crunch situations.

Note – You can save the queries in <file_name>.sql and then call them in your command promptt.

Getting all the session information.

col SPID for a15
col Sid/Serial# for a15
col MODULE for a35
col SQL_ID for a15
col username for a15
col PROGRAM for a39
col TIME for a20
select s.inst_id,p.spid, s.sid || ',' || s.serial# "Sid/Serial#", s.sql_id,s.module, s.username, s.program,
to_char(s.logon_time, 'DD-Mon-YY HH24:MI:SS' ) Time
from
gv$process p, gv$session s
where
p.addr = s.paddr
and s.sid = '&sid'
order by Time;

You can get the script for getting current events going on in the database. Click-here for the script.

Getting oracle session information based on OS PID

select s.inst_id,s.sid, s.serial#, s.username,
to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
s.program clientprogram, s.module, s.machine, s.osuser,
s.status, s.last_call_et
from gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr and s.inst_id=p.inst_id
order by s.sid;

Kill scripts based on SQL_ID and INST_ID

col str for a60
col SQL_ID for a25
select 'alter system kill session '||''''||s.sid||','||s.serial#||',@'||s.inst_id||''''||' immediate; ' str
from gv$session s
where
s.inst_id='&inst_id'
and s.sql_id ='&sql_id';

Kill script based on SID and SERIAL#

select 'alter system kill session '''||sid||','||serial#||''' /* '
||username||'@'||machine||' ('||program||')*/ immediate;' commands_to_verify_and_run
from v$session
where sid='&sid' and serial#='&serial';

Kill script for terminating OS process

set pages 0
select 'kill -9 '||b.spid
from v$session a,v$process b
where a.paddr(+)=b.addr
and a.sid ='&sid'
and b.pid<>1;