Identify Blocking Sessions

select
x.inst_id WAITING_INSTANCE,
x.sid WAITING_SID,
w.username WAITING_USER,
w.machine WAITING_MACHINE,
w.program WAITING_PROGRAM,
w.module WAITING_MODULE,
(select distinct sql_text from v$sql where sql_id=w.sql_id) WAITING_SQL,
w.sql_exec_start WAITING_SQL_START,
w.seconds_in_wait WAITING_SECONDS,
w.blocking_session BLOCKING_SID,
b.inst_id BLOCKING_INSTANCE,
b.username BLOCKING_USER,
b.machine BLOCKING_MACHINE,
b.program BLOCKING_PROGRAM,
b.module BLOCKING_MODULE,
(select distinct sql_text from v$sql where sql_id=b.prev_sql_id) BLOCKING_SQL,
b.prev_exec_start BLOCKING_SQL_START
from
GV$lock x, -- waiting lock
gv$lock y, -- blocking lock
gv$session w, -- waiting session
gv$session b -- blocking session
where
x.request>0
and w.sid=x.sid
and y.block>0
and b.sid=y.sid
and x.id1=y.id1
and x.id2=y.id2
and w.blocking_session=b.sid
and y.addr=b.taddr