Blocking session

col BLKD for a10
col BLKR for a10
col BLKDUSR for a10
col BLKRUSR for a10
col BLKDPRG for a12
col BLKRPRG for a12
col WHY for a20
col OBJECT for a15
col STATUS for a10
prompt "=*=*=*=*=*=Blocked and Blocking sesssion =*=*=*=*=*="

select a.inst_id||' - '||a.sid BLKD, a.username BLKDUSR, a.program BLKDPRG, a.blocking_instance||' - '||a.BLOCKING_SESSION BLKR,
       b.username BLKRUSR, b.program BLKRPRG, a.event||' - ran '||act.name WHY, t.start_time, t.status, obj.owner||'.'||obj.object_name object, obj.object_type
  from gv$session a,
       gv$session b,
       gv$transaction t,
       gv$locked_object lobj,
       dba_objects obj,
       audit_actions act
 where a.blocking_session is not null
   and a.blocking_instance=b.inst_id
   and a.blocking_session=b.sid
   and t.inst_id=b.inst_id
   and t.ses_addr=b.saddr
   and lobj.inst_id=b.inst_id
   and lobj.session_id=b.sid
   and lobj.object_id=obj.object_id
   and a.command=act.action;

prompt "=*=*=*=*=*=Blocking sesssion =*=*=*=*=*="

select
(select username from gv$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from gv$session where sid=b.sid) blockee,b
.sid
from gv$lock a, gv$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

output
BLKD       BLKDUSR    BLKDPRG      BLKR       BLKRUSR    BLKRPRG      WHY                  START_TIME           STATUS     OBJECT
---------- ---------- ------------ ---------- ---------- ------------ -------------------- -------------------- ---------- ---------------
OBJECT_TYPE
-------------------
1 - 956

select sid,SERIAL#,status,username from v$session where sid=956;
alter system kill session '956, 32834' immediate;