Oracle Holding And Waiting Session

oracle_holding_and_waiting_sessions.sql

select 
    holders.*,
    ' IS BLOCKING ' is_blocking,
    waiters.*
from
    (
        select 
            dw.holding_session,
            hs.username, 
            do.object_name, 
            hs.row_wait_obj#,
            hs.row_wait_file#,
            hs.row_wait_block#,
            hs.row_wait_row#,
            case when hs.row_wait_obj# > 0 and hs.row_wait_file# > 0 and hs.row_wait_block# > 0 then
                dbms_rowid.rowid_create(1,hs.row_wait_obj#,hs.row_wait_file#,hs.row_wait_block#,hs.row_wait_row#)
            else
                null
            end row_id,
            s.sql_id,
            s.sql_text
        from 
            dba_waiters dw,
            v$session hs,
            dba_objects do,
            v$sql s
        where 
            dw.holding_session = hs.sid
        and 
            do.object_id(+) = hs.row_wait_obj#
        and
            s.sql_id(+) = hs.sql_id
    ) holders,
    (
        select 
            dw.waiting_session, 
            ws.username, 
            do.object_name, 
            ws.row_wait_obj#,
            ws.row_wait_file#,
            ws.row_wait_block#,
            ws.row_wait_row#,
            case when ws.row_wait_obj# > 0 and ws.row_wait_file# > 0 and ws.row_wait_block# > 0 then
                dbms_rowid.rowid_create(1,ws.row_wait_obj#,ws.row_wait_file#,ws.row_wait_block#,ws.row_wait_row#)
            else
                null
            end row_id,
            s.sql_id,
            s.sql_text
        from 
            dba_waiters dw, 
            v$session ws, 
            dba_objects do, 
            v$sql s
        where 
            dw.waiting_session = ws.sid
        and 
            do.object_id = ws.row_wait_obj#
        and 
            s.sql_id(+) = ws.sql_id
    ) waiters,
    dba_waiters 
where 
    holders.holding_session = dba_waiters.holding_session
and
    waiters.waiting_session = dba_waiters.waiting_session;