Follow the steps to locate and terminate blocking session from Oracle database (10g & up). Blocking_session_status contains ‘VALID’ when blocking_session is populated in 10g and up.
steps:
1. Display blocked session and their blocking session details
2. Find what is Blocking session Doing
3. Find sid and serial # of blocking session
4. To terminate the session:
1. Display blocked session and their blocking session details.
Script : blocking.sql
-- blocking.sql
Select blocking_session, sid, serial#, username, event, wait_class,
seconds_in_wait siw, blocking_session_status
from v$session where blocking_session is not NULL order by blocking_session;
blocking_session provides the sid which is blocking
sid and serial is of the blocked sessions
2. Find what is Blocking session Doing
Script : sql_text.sql
-- sql_text.sql
break on sql_address
SELECT s.sql_address, t.sql_text
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sid = &1 and s.sql_address = t.address
AND s.sql_hash_value = t.hash_value
ORDER BY t.address, t.hash_value, t.piece;
# enter blocking_session # from step 1
3. Find sid and serial # of blocking session
Script : Sessions.sql
set linesize 132 wrap off pagesize 999
Prompt "Oracle Sessions"
col sid format 99999 Head "SID"
col spid format a6 Head "OSPID"
col program format a20 Head "Program"
col osuser format A08 Head "OS User"
col user format A08 Head "User"
col module format A15 Head "Module"
col action format A25 Head "Action"
col logon_time format A12 Head "Logon Time"
col background format A2 head "B"
col lockwait format A2 head "XW"
col latchwait format A2 head "LW"
col machine format a6 head client
col process format a6 head 'Cl Pid'
col elapsed format a7 head 'elapsed'
select sid,
s.serial#,
spid ,
substr(osuser,1,12) osuser,
substr(s.username,1,15) "user",
status,
-- decode(p.background,'1','Y','N') background,
-- decode(s.lockwait,null,'N','Y') lockwait,
-- decode(p.latchwait,null,'N','Y') latchwait,
substr(to_char(logon_time,'dd-mm hh24:mi'),1,12) logon_time
,substr(module,1,45) module
,machine
,process
,substr(to_char(last_call_et),1,7) elapsed
,decode(s.blocking_session,null,'N','Y') blk
-- ,substr(module,1,45) module
-- ,action
from v$session s,
v$process p
where s.username not in (' ')
and p.addr=s.paddr
order by status desc, logon_time
/
set lines 80 wrap on pagesize 23
4. To terminate the session:
Script : drop_session.sql
alter system kill session 'sid, serial#’; # from step 3
Hope this helps! Rupam
