Blocking Locks

In order to identify who is blocking a session in Oracle, try the following example.

 

1.       Create a table and then lock it.

create table tab1 (acolumn date);
lock table tab1 in exclusive mode;

2.       Now from another session, try to insert into it.

insert into tab1 select sysdate from dual;

Of course because of the lock, this session will now hang waiting for the lock to be released.

3.       From a third session, take a look at the blocking lock.

set linesize 132
set heading off
col “Session 1” for a50
col “Session 2” for a50
select sess1.username || ' on ' || sess1.machine || ' (' || sess1.sid || '/' || sess1.serial# || ') ' ||
'Is blocking ' ||
sess2.username || ' on ' || sess2.machine || ' (' || sess2.sid || '/' || sess2.serial# || ') ' ||
' => ' || dobj1.owner || '.' || dobj1.object_name || ' (' || dobj1.object_type || ')' "object"
from v$lock    lock1,
v$session sess1,
v$lock    lock2,
v$session sess2,
v$locked_object lobj1,
dba_objects dobj1
where sess1.sid=lock1.sid and sess2.sid=lock2.sid
and lock1.BLOCK=1 and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2
and sess1.sid = lobj1.session_id
and lobj1.object_id = dobj1.object_id
/
set heading on


Which will return something like this. I included the sid/serial# so you can alter session kill ‘sid/serial#’ immediate; if you need to.

SYS on U-DOM1\HP20014881 (64/3207)    Is blocking  SYS on U-DOM1\HP20014881 (307/3513)

4.       Finally release the lock. On the original ‘lock’ session.

rollback;

You might want to rollback or commit the insert session and drop the table.

Happy Heth