Blocking Sessions Queries

All Blocking sessions
--------------------------
select sid "waiter",a.inst_id "Waiting_Inst", username, a.sql_id "Blocked sql", 
b.sql_text,blocking_session "blocker",blocking_instance "Blocking_Inst", 
seconds_in_wait "secs wait", wait_time
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null
order by seconds_in_wait desc;



Blocked sessions
-------------------------
select sid "waiter",a.inst_id "Waiting_Inst", username, a.sql_id "Blocked sql", 
b.sql_text,blocking_session "blocker",blocking_instance "Blocking_Inst", 
seconds_in_wait "secs wait", wait_time
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null
and a.blocking_session not in (
select sid 
from gv$session a , gv$sql b
where state in ('WAITING')
and wait_class != 'Idle'
and a.sql_id=b.sql_id
and a.inst_id=b.inst_id and blocking_session is not null))


Blocked Rows
------------------
SQL> column  object_name format a30
SQL> SELECT do.owner,do.object_name ,
       s.row_wait_obj#  ,
       s.row_wait_file# ,
       s.row_wait_block#,
       s.row_wait_row#  ,
       dbms_rowid.rowid_create ( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
FROM   gv$session s,
       dba_objects do,
       v$datafile v
WHERE  s.blocking_session = 582                         
AND    s.row_wait_obj# = do.object_id
AND    s.row_wait_file# = v.file#
;​