Locks in Database-2

Below is the query that can help you with multiple enqueue.
set lines 120
set colsep "    "
col "HOLDER - SID| Module| Event" for A55
col "WAITER - SID| Module| Event" for A55
select
(a.sid||'| '||substr(a.module,1,20)||'| '||substr(a.event,1,26)) as "HOLDER - SID| Module| Event",
(b.sid||'| '||substr(b.module,1,20)||'| '||substr(b.event,1,26)) as "WAITER - SID| Module| Event"
from v$session a,
(select  sid, blocking_session,event,module  from v$session where blocking_session_status='VALID') b
where a.sid=b.blocking_session;

For RAC databases:-

set linesize 100
column Waiting(inst|sid|module) format A25
column Blocking format A20
column id1 format 999999999
column id2 format 999999999
column Req-Mode format 99
column Hold-Mode format 99
select substr(la.inst_id,1,1)||'|'||la.sid||'|'||substr(sa.module,1,9) "Waiting(inst|sid|module)",
substr(lb.inst_id,1,1)||'|'||lb.sid||'|'||substr(sb.module,1,9) "Blocking",
la.id1 "ID1", la.id2 "ID2", la.request "Req-Mode" ,lb.lmode "Lock-Mode"
from gv$lock la, gv$lock lb , gv$session sa, gv$session sb
where
la.id1=lb.id1
and la.id2=lb.id2
and la.request=6
and lb.lmode=6
and sa.sid=la.sid
and sa.inst_id=la.inst_id
and sb.sid=lb.sid
and sb.inst_id=lb.inst_id
order by 2;

Query above doesn't show the library locks and pins, and that can be checked in the view dba_kgllock
Below query can be useful in that case

select  s1.sid , s2.sid ,l1.kgllktype lock_or_pin,l1.kgllkhdl address,
decode(l2.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held,
decode(l1.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock l1, dba_kgllock l2, gv$session s1, gv$session s2
where (((l2.kgllkmod != 0) and (l2.kgllkmod != 1)
and ((l2.kgllkreq = 0) or (l2.kgllkreq = 1)))
and
(((l1.kgllkmod = 0) or (l1.kgllkmod= 1))
and ((l1.kgllkreq != 0) and (l1.kgllkreq != 1))))
and  l1.kgllktype = l2.kgllktype
and  l1.kgllkhdl = l2.kgllkhdl
and  l1.kgllkuse = s1.saddr
and  l2.kgllkuse = s2.saddr;

As we find the details of sessions the same can be checked further and the relevant can be killed as

SQL> select a.sid, b.spid , a.serial# from v$session a, v$process b where a.sid in (<sid>) and a.paddr=b.addr;
Get the spid and check as below on OS
$ ps –ef | grep spid
And then use below to kill
$ kill -9 <spid>
Or Kill the oracle session using
SQL> ALTER SYSTEM KILL SESSION '<sid>, <serial#>';

Sometimes killing session from either one may not be enough and you need to kill both the OS process and the oracle session.