在Oracle的存储过程中如何执行杀掉某进程的命令(orakill oracle_sid 1234)??
我想写一个存储过程,定期杀掉另外一个存储过程被死锁的进程。代码如下:
CREATE OR REPLACEPROCEDURE P_Testas
myRecnum number(10);
mySID number(10);
mySpid number(10);
cursor c_test is SELECT SID FROM V$ACCESS WHERE OWNER='AAA' AND object = 'PROC1' and type = 'PROCEDURE';
begin
myRecnum:=0;
SELECT count(*) into myRecnum FROM V$DB_OBJECT_CACHE WHERE OWNER='AAA' AND name = 'PROC1' andLOCKS'0';
if myRecnum > 0 then
open c_test;
fetch c_test into mySID;
while c_test%FOUND LOOP
select spid into mySpid from v$session s,v$process p where s.paddr=p.addr and s.sid = mySID;
--杀掉进程
orakill Oracle_SID mySpid ;
fetch c_test into mySID;
end LOOP;
close c_test;
end if;
end;
看看这个如何:
CREATE OR REPLACEPROCEDURE P_Testas
myRecnum number(10);
mySID number(10);
mySpid number(10);
mySerial number(10);
myPaddr number(10);
cursor c_test is SELECT SID FROM V$ACCESS WHERE OWNER='AAA' AND object = 'PROC1' and type = 'PROCEDURE';
begin
myRecnum:=0;
SELECT count(*) into myRecnum FROM V$DB_OBJECT_CACHE WHERE OWNER='AAA' AND name = 'PROC1' andLOCKS'0';
if myRecnum > 0 then
open c_test;
fetch c_test into mySID;
while c_test%FOUND LOOP
select serial# into mySerial from v$session where sid=mySID;
select paddr into myPaddr from v$session where sid=mySID;
select spid into mySpid from v$process paddr=myPaddr;
--杀掉进程
exec immediate 'alter system kill session 'mySID,mySerial'';
orakill Oracle_SID mySpid ;
CREATE OR REPLACEPROCEDURE P_Testas
myRecnum number(10);
mySID number(10);
mySpid number(10);
mySerial number(10);
myPaddr number(10);
cursor c_test is SELECT SID FROM V$ACCESS WHERE OWNER='AAA' AND object = 'PROC1' and type = 'PROCEDURE';
begin
myRecnum:=0;
SELECT count(*) into myRecnum FROM V$DB_OBJECT_CACHE WHERE OWNER='AAA' AND name = 'PROC1' andLOCKS'0';
if myRecnum > 0 then
open c_test;
fetch c_test into mySID;
while c_test%FOUND LOOP
select serial# into mySerial from v$session where sid=mySID;
select paddr into myPaddr from v$session where sid=mySID;
select spid into mySpid from v$process paddr=myPaddr;
fetch c_test into mySID;
--杀掉进程
exec immediate 'alter system kill session 'mySID,mySerial'';