SQL statement to find blocking session

Use SQL to identify and monitor the database session that blocks other session:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, 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;

Use below to confirm the blocking status in the database:

SQL> SELECT blocking_session block_holder,

  sid, serial#, process, machine, client_identifier, wait_class, seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;