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!

Advertisements

One thought on “Deadlock Resolution: Killing Oracle Sessions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s