Deadlock Resolution: Killing Oracle Sessions

 

We usually face this situation where an Oracle session is dead and an UPDATE command freezes forever. Usually it is the fault of the programmer who messes up with his session. In such case of a deadlock, the problem session needs to be killed.

Following is a query (I got from some other blog I don’t remember) we use to find objects which are locked. This is used to make sure that the object which we are trying to update is actually locked by another session:

select
   c.owner,
   c.object_name,
   c.object_type
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

And this query tells which are the problem sessions:

select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

So now we have the SID of the problem session and we only need the serial number to kill it.

SELECT serial# FROM v$session WHERE sid=<sid>

And now kill it:

ALTER SYSTEM KILL SESSION ‘<sid>,<session#>’

Easy!

 

 

Share this post : MSDN! Technet! del.icio.us it! digg it! Facebook it! live it! reddit! technorati!