Find Blocking Session, Locks, Waiting Session of Database

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

SQL> SELECT SID, DECODE(BLOCK, 0, ‘NO’, ‘YES’ ) BLOCKER,

DECODE(REQUEST, 0, ‘NO’,’YES’ ) WAITER

FROM V$LOCK

WHERE REQUEST > 0 OR BLOCK > 0 ORDER BY block DESC;

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

SQL> select * from dba_blockers;

SQL> select BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION from V$session;

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

SQL> SELECT SID, DECODE(BLOCK, 0, ‘NO’, ‘YES’ ) BLOCKER,

DECODE(REQUEST, 0, ‘NO’,’YES’ ) WAITER

FROM V$LOCK

WHERE REQUEST > 0 OR BLOCK > 0 ORDER BY block DESC;