有时候死锁时需要杀死的进程有太多,一个个杀会很麻烦。
因此考虑用语句进行批量杀
这里要用到的是 单引号与变量拼接,我最初一直卡在这里。在网上搜到http://blog.csdn.net/firetaker/article/details/5666634后才解决
最初版:
declare cursor mycur is
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
begin
for cur in mycur
loop
select count(1) into newcount from v$locked_object a,v$session b
where a.session_id = b.sid and b.sid=cur.sid and b.serial#=cur.SERIAL# order by b.logon_time;
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
提示会话id不存在,刚开始想不通,后来想通了,应该是select语句查出来的sid,serial#存在重复的情况,前面已经kill掉了以后,后面再对这个sid,serial#进行kill就会提示不存在了,所以考虑每次实时进行查询是否还存在,在execute前加一个判断。
改版:
declare cursor mycur is
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
newcount number;
begin
for cur in mycur
loop
select count(1) into newcount from v$locked_object a,v$session b
where a.session_id = b.sid and b.sid=cur.sid and b.serial#=cur.SERIAL# order by b.logon_time;
if newcount>=1 then
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end if;
end loop;
end;
执行后发现有效果,但是很慢。
所以想到直接避免循环中每次再去查询:
declare cursor mycur is
select b.sid,b.serial#
from v$locked_object a,v$session b
where a.session_id = b.sid group by b.sid,b.serial#;
begin
for cur in mycur
loop
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;