James's Blog

Oracle: transformation between v$lock and v$session_wait

enqueue
-- P1: lock type(or name) and mode
-- P2: resource identifier ID1 for the lock
-- P3: resource identifier ID2 for the lock

v$lock.id1 = p2
v$lock.id2 = p3

--lock type:
v$lock.type = chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,1671680)/65535)

--the mode in which the enqueue is being requested
v$lock.request = mod(p1,65536)

--find locks and lock holders
to show only holders and waiters for locks being waited on
select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1,
id2,
lmode,
request,
type
from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request > 0)
order by id1, request;

posted by James Pei @ 5:18 PM