Oracle Locks

Blocking locks can be a real problem, the sql below gives a nice simple view of any you may have.

 

select s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' ) is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  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 l1.id2 = l2.id2 ;

 

Sometimes you get distributed locks, and you need to kill the os process, the sql below gives you the os process id so you can use kill -9 <process id>

select p.spid from v$session s 
 inner join v$process p on (s.paddr=p.addr)
 where s.osuser='<user>';