Posts tagged 11g
How to find blocking sessions in Oracle (11g). 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#>'