Find Locks or Identifying sessions holding or acquiring the lock

Finding Locked Objects : (Identifier for session holding or acquiring the lock)

For more info regarding locks : search in google :v$lock.

SQL> 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;

SESS                                                    ID1        ID2

------------------------------------------------ ---------- ----------

     LMODE    REQUEST TY

---------- ---------- --

Holder: 336                                         1376264      27170

         6          0 TX

Waiter: 32                                          1376264      27170

         0          6 TX

Find the serial number of sid from v$session; then kill the session  and release the lock ?

Select serial# from v$session where sid=336;


O/p
Serial#

121

Now Kill the session

alter system kill session '336,121' immediate;

Which Sid is blocking which Sid ?

select l1.sid,'IS BLOCKING',l2.sid

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

 SID 'ISBLOCKING        SID

---------- ----------- ----------

336 IS BLOCKING         32

Show sid blocking plus machine name also ?

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 l2.id2 = l2.id2 ;

BLOCKING_STATUS

--------------------------------------------------------------------------------

APPS@maxindia-9a85e7 ( SID=336 )  is blocking APPS@erpdev ( SID=32 )