To find Blocking Session

 To find Blocking Session

========================================

col WAIT_CLASS for a12;

select blocking_session,sid,serial#,wait_class,seconds_in_wait,status

from v$session where blocking_session is not NULL order by blocking_session;

select sid,serial#,event,module,action,status,program,blocking_session,last_call_et/60 min from v$session where sid=&sid;

select p.spid,s.sid,s.serial#,s.status,s.last_call_et,s.module,s.osuser from V$process p,V$session s where s.paddr = p.addr and p.spid=&pid;

SQL> alter system kill session '5213,45198';

To find out work flow blocking session 

select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from V$session where module like '%WFBG%';

**********************************************************************************

Blocking session 

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;

********************************************************************************

BLocking session

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 ;

************************************************************************

Blocking session

select do.object_name,

  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

  from v$session s, dba_objects do

  where sid=543

  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

alter system kill session 'SID,SERIAL#';