Managing Blocking Sessions

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;


Select -- s.username, s.LOGON_TIME,
'alter system kill session '''||s.sid||','||s.serial#||''';' "Kill Statement"
FROM V$SESSION s , V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.last_call_et > 1000
and s.status='INACTIVE';



Select s.username, s.LOGON_TIME
FROM V$SESSION s , V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.last_call_et > 172800
and s.status='INACTIVE';


Select area.sql_text from v$sqlarea area,v$session sess
where area.hash_value=sess.sql_hash_value and sess.sid=   2  1040;

SQL_TEXT
--------------------------------------------------------------------------------
insert into CM_PRC_DYN_ANS                 (VERSION, DESCRLONG, SELECT_SW, CM_PR
C_ID, CM_QUEST_TYP_CD, CM_ID, SEQNO) values (:1, :2, :3, :4, :5, :6, :7)


SQL> Select area.sql_text from v$sqlarea area,v$session sess
where area.hash_value=sess.sql_hash_value and sess.sid=  2  1093;

SQL_TEXT
--------------------------------------------------------------------------------
begin :f_scn := 0; dbms_capture_adm.build(:f_scn); dbms_output.put_line('The fir
st_scn value is ' || :f_scn); end ;


Library cache lock – find locking session
--------------------------------------------
1.select saddr from v$session where sid in (select sid from v$session_wait where event like ‘library cache lock’);

2. FIND BLOCKER:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'result_from_1' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

3. FIND BLOCKED:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
); 

To find a sessions SPID using the SID:
---------------------------------------
select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(XXXX)
order by s.sid;