Skip to content

script to kill blocking sessions – for the new oracle dba




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;

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.