Killing Session in Oracle

As a DBA you may come across requirements to kill blocking session and facilitate the next user in the que to take ownership and work. Killing sessions in Oracle can be achieved at 2 levels, that is either at Database level or at OS level. Lets look into how this could be achieved in these 2 different levels.

Killing Session at Database Level.

1. Issue the following command to obtain information which client machine and on which OS username and on which database user login is blocking the session for the next queued user.

col BLOCKING_STATUS for a55
col "Kill blocking session Query" for a50


SELECT s1.username || '@' || s1.machine || ' (SID='||s1.SID||') is blocking ' || s2.username || '@' || s2.machine || ' (SID='||s2.SID||')' AS blocking_status,
q'[alter system kill session ']'||s1.sid||','||s1.SERIAL#||q'[']' as "Kill blocking session Query"
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

2. After obtain the user who is blocking the session, kill the session by executing the respective query by just copying and pasting from the "Kill blocking session query" column.


Killing Session at OS Level.

The Unix Approach.

1. Issue the following query to find out the information on which session to be killed.

SELECT s.sid, s.serial#, p.spid, s.username
FROM gv$session s join gv$process P
ON p.addr = s.paddr
AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

2. After identifying which user is blocking the respective session kill the session by providing the spid that was obtained from the above query (You will have to be a superuser to issue the following command).

kill -9 <spid> 


The Windows Approach.

1. To kill the session on the windows operating system, first identify the session (which can be obtained by issue the query that is given above in the Unix approach), then substitute the relevant SID and SPID values inorder to kill the session.


orakill <SID> <SPID>

The above will result in kill immediately and all resources will released immediately.

IMPORTANT POINTS TO NOTE:


Note 1:  The above OS level approaches has the capability to even kill sys users session with Oracle if your through background processess .


Note 2: The latest sessions will be in the top while the old sessions will remain at the bottom. Basically the latest session will be having the latest SID.




Hope this help!!!