below is a useful script to kill blocking sessions in your database.
declare
v_sid number :=10;
v_serial number;
begin
while v_sid > 0
loop
begin
SELECT b.sid ,(select a.serial# from v$session a where a.sid = b.sid) into v_sid,v_serial
FROM V$LOCK b
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) and b.request = 0 and rownum = 1;
execute immediate ‘alter system kill session ”’||v_sid||’,’||v_serial||””;
exception
when others then v_sid := 0;
exit;
end;
end loop;
end;
