Cyclic blocking session removal script written by me for Oracle Database.

Plenty of times we have cyclic database blocking sessions. We have the script which runs in loop and kills them , only to see new blocking sessions have resurfaced.

This is a classic scenario that happens many time due to poor application design.

A was blocking B , B was blocking C.

Unless you have some automated blocking session clearing script.

You would run a script at point in time killing session of A.
Giving the lock ownership to B.   B could realize it late that he has the lock and not commit his work.
So DBA again goes in and sees block kills B's session.

In meantime Frustrated A logs in and start his activity again queuing him up in wait for lock retrieval . This goes on and you end up running the script 5-10 times to kill these sessions to finally clear the blocks.

Note this is not a deadlock which is normally apprehended and identified by oracle.

In such case we had written below plsql to clear the sessions. This basically goes in and check for locks every 2 seconds until all blocks are cleared.

BEWARE YOU WILL NOT HAVE CONTROL ON WHICH SESSION ARE BEING KILLED AS SOON AS SESSION BECOMES BLOCKING IT WILL BE REMOVED IF YOU USE BELOW SCRIPT. IMPORTANT SESSIONS COULD BE KILLED SO BE EXTREMELY CAREFUL AND I RECOMMEND NOT USING IT ON PRODUCTION WITHOUT TWEAKING THE FILTER FOR SPECIFIC SESSIONS OR USERNAMES IN WHERE CLAUSE.

set serverout on
declare
cursor c1 is
select  v.process,b.SQL_ID,  v.SID ,v.SERIAL#,b.SECONDS_IN_WAIT,v.username 
from        v$session v,user_definition_table ud,v$session b ,user_definition_table ubd
where      v.username not in ('SYS')
 and v.username=ud.user_name(+)
 and b.username=ubd.user_name
 and v.SID=b.FINAL_BLOCKING_SESSION
 and b.FINAL_BLOCKING_SESSION_STATUS='VALID'
 and v.status <> 'KILLED';
begin
for i in 1 ..100 
loop
dbms_lock.sleep(2);
for c1_rec in c1
loop
begin
dbms_output.put_line('USER ' || c1_rec.username);
execute immediate 'alter system kill session '''||  c1_rec.SId ||','|| c1_rec.SERIAL# ||''' immediate';
exception when others then
null;
end;
end loop;
end loop;
end;
/