宝塔服务器面板，一键全能部署及管理，送你10850元礼包，点我领取

查杀系统死锁的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 表或视图不存在

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登录,可以正常创建存储过程.

<p>比如我会在文章中加 <a href=”http://www.cnblogs.com/TankXiao/” target=”_blank“>[作者：testtest]</a> &nbsp; 如图</p>