For blocking session most important is to find out which session is getting blocked and which is holding the session.
most of the time seeing one session is blocking many other session so to handle this type of situations use this method to clear the blocks.
steps:
1) select instance_name||' - '||status||' - '||startup_time||' - '||host_name||' - '||sysdate from v$instance;
to confirm the db name are you in correct database.
2) select sid from v$lock where block=1;
this query gives you the sid's which are getting effected.
3) find the sql text for this sessions.
select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');
4) check for the other detials like their sid, serail#, osuser, machine and their status ( Active / Inactive) by passing the sid from previous query of step 2.
check for all sid's from all of the results you may not get any sql text with one sid which will be active in status that is the one main culprit blocking session which is holding lock for other sessions to execute.
select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;
Identify the holder session which is active for more confirmation you can also check the holders and waiters.
check holders & waiters:
========================
set pagesize 100
select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from v$lock
where (id1, id2, type) IN (SELECT id1, id2, type from v$lock where request>0) ORDER BY id1, request;
This query results with more details the top one is the holder and others are waiters.
which is notthing but the active session which you can see with the pervous query.
5) kill the holder session
ALTER SYSTEM KILL SESSION '&sid, &serial';
or run this.
SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ''';' a,
'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID,
'kill -9 ' || p.SPID
FROM gv$session s, gv$process p
WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id))
AND s.sid = &sid;
you will get the result like
alter system kill session '123, 32422';
ps -ef |grep LOCAL=NO|grep 234223
kill -9 234223
use any one of the result to clear the lock.
Most of the time a DBA can not directly kill the locks, in this situation consult with the application team to clear the lock and use this easy steps to clear the lock.
