Oracle杀死死锁进程

查杀系统死锁的sql,最近想改造成存储过程,如下:

CREATE OR REPLACE PROCEDURE HERO_KILLLOCKSESSION

 

(OUT_COUNT OUT NUMBER, OUT_CUR OUT ZHUOCAIDEV.FUXJPACKAGE.FUXJRESULTSET) IS

 

BEGIN

 

  SELECT COUNT(*)

    INTO OUT_COUNT

    FROM V$LOCKED_OBJECT L, DBA_OBJECTS B, V$SESSION X, V$PROCESS C

   WHERE B.OBJECT_ID = L.OBJECT_ID

     AND L.SESSION_ID = X.SID

     AND C.ADDR = X.PADDR;

 

  OPEN OUT_CUR FOR

    SELECT B.OWNER,

           B.OBJECT_NAME,

           L.SESSION_ID,

           X.SERIAL#,

           X.USERNAME,

           X.LOGON_TIME,

           L.LOCKED_MODE,

           'alter system kill session ''' || X.SID || ',' || X.SERIAL# ||'''' KILLSQL,

           C.SPID AS OS_PROCESS_ID,

           C.PID,

           'ps -ef | grep ' || C.SPID AS CHECK_OS_PROCESS_COMMAND,

           'kill -9 ' || C.SPID AS KILL_OS_PROCESS_COMMAND

      FROM V$LOCKED_OBJECT L, DBA_OBJECTS B, V$SESSION X, V$PROCESS C

     WHERE B.OBJECT_ID = L.OBJECT_ID

       AND L.SESSION_ID = X.SID

       AND C.ADDR = X.PADDR

     ORDER BY X.LOGON_TIME ASC;

 

END;

在系统sys模式下可以正常运行,在zhuocaidev下面报 ORA-00942 表或视图不存在

但在正常的SQL中是能够对V$LOCKED_OBJECT , DBA_OBJECTS , V$SESSION , V$PROCESS 进行访问的。猜测估计是权限问题,联想到运行statspack报告的perfstat用户没有此问题，查看下statspack的创建脚本，发现需要单独使用下面语句进行赋权：

 

 

grant select on V_$SESSION to scott;

以sys用户登录,执行如下授权语句:

grant select on SYS.V_$LOCKED_OBJECT TO zhuocaidev; grant select on  SYS.DBA_OBJECTS TO zhuocaidev; grant select on SYS.V_$SESSION  TO zhuocaidev; grant select on SYS.V_$PROCESS TO zhuocaidev;

在以zhuocaidev登录,可以正常创建存储过程.

 

 

 

 

 

 

create or replace procedure pro_kill_lockprocess is /*      以sys用户登录,执行如下授权语句: grant select on SYS.V_$LOCKED_OBJECT TO gtdj; grant select on  SYS.DBA_OBJECTS TO gtdj; grant select on SYS.V_$SESSION  TO gtdj; grant select on SYS.V_$PROCESS TO gtdj; */   v_sql varchar2(4000); begin   for rec1 in (SELECT /*+ rule */                 s.username,                 l.type,                 decode(l.type,                        'TM',                        'TABLE LOCK',                        'TX',                        'ROW LOCK',                        NULL) LOCK_LEVEL,                 o.owner,                 o.object_name,                 o.object_type,                 s.sid,                 s.serial# serial,                 s.terminal,                 s.machine,                 s.program,                 s.osuser,                 s.status                 FROM v$session s,v$lock l,dba_objects o                 WHERE l.sid = s.sid                   AND l.id1 = o.object_id(+)                   AND USERNAME = 'GTDJ'                   and object_name = 'ZX_GTDJ_LICENSE_TEMP'                   AND s.username is NOT NULL                 order by l.type) loop       v_sql := 'alter system kill session ''' || rec1.sid || ',' ||              rec1.serial || '''';     dbms_output.put_line(v_sql);     execute immediate v_sql;   end loop;

 

end pro_kill_lockprocess;

 

 

 

 

批量杀死进程：

declare v_sql varchar2(4000); begin   for rec1 in (SELECT /*+ rule */ s.username, l.type,

decode(l.type,'TM','TABLE LOCK',

               'TX','ROW LOCK',

               NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial# serial,s.terminal,s.machine,s.program,s.osuser,s.status

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+) AND USERNAME='GTDJ' and object_name='ZX_GTDJ_LICENSE_TEMP'

AND s.username is NOT NULL order by l.type ) loop

v_sql := 'alter system kill session '''||rec1.sid|| ','||rec1.serial||''''; dbms_output.put_line(v_sql); execute immediate v_sql; end loop;

end;