Method-1:
Below query gives you the sid's which are getting effected.
1)select sid from v$lock where block=1;
2) 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');
3) 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=&sid;
Identify the holder session which is active for more confirmation you can also check the holders and waiters.
check holders & waiters:
========================
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';
Method-2:
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 'SID, SERIAL#';
ps -ef |grep LOCAL=NO|grep PID
kill -9 ProcessID
