Bloking Locks

SQL query to identify blocking locks:

1.
select s.username "Blocking Username",l1.sid "Blocking SID", l2.sid "Blocked SID",s2.username "Blocked Username"
from v$lock l1, v$lock l2,v$session s,v$session s2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2
and l1.sid=s.sid
and l2.sid=s2.sid;

2.
select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive',l.request) Lock_Req_In, l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
from v$lock l
where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc;


Kill The Blocking Session

Find the 'serial' number of bloking session:
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status
from v$process p, v$session s
where s.paddr = p.addr (+) and s.sid = '&sid';

Use alter system command to kill the session:
Alter system kill session '115,10366' immediate;