blocking session

select sid, username, serial#, process, nvl(sql_id, 0), sql_address, blocking_session, wait_class, event, p1, p2, p3, seconds_in_wait from v$session where blocking_session_status = 'VALID' OR sid IN (select blocking_session from v$session where blocking_session_status = 'VALID');

another query
--------------------------
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
/

The output will be something like the below

SID 'ISBLOCKING' SID
--- ------------- ------
422 IS BLOCKING 479


Another query
------------------------
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 ;


The output will be like this :

BLOCKING_STATUS
-----------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )