杀掉正在执行的存储过程

一、问题描述：应用人员有时会报，有的存储过程有问题（死循环）或一直在执行。这时我们需要查到对应的PID将其杀之。现做如下实验（前提是存储过程正在执行，知道用户名和存储过程名）

--授权，然后以zhaomn用户创建存储过程

grant execute on dbms_lock to zhaomn;

二、具体操作

1.创建存储过程

create or replace procedure zhaomn.A_T1 as

begin

DBMS_LOCK.sleep(200);

END;

2. CRT下打开一个新的窗口，查看sid

 

 

SQL> select distinct sid from v$mystat;

 

       SID

----------

       106

 

 

3.执行存储过程

 

execute zhaomn.A_T1

4.打开新窗口

SQL>  col OWNER for a10

SQL> col OBJECT for a10

SQL> col TYPE for a10

SQL> set line 300

SQL>  SELECT * FROM   V$ACCESS WHERE   OWNER = 'ZHAOMN' AND OBJECT = 'A_T1';

 

       SID OWNER      OBJECT     TYPE

---------- ---------- ---------- ----------

       106 ZHAOMN     A_T1       PROCEDURE

5.sid对应的pid

SQL> select spid

  2     from v$process

  3  where addr=(select paddr from v$session where sid=&sid);

Enter value for sid: 106

old   3: where addr=(select paddr from v$session where sid=&sid)

new   3: where addr=(select paddr from v$session where sid=106)

 

SPID

------------------------

12537

6.杀之

Kill -9 12537

7.补充，先找出cache中锁住的对象，存储过程正在执行时，是被锁定的

SQL>  col owner for a10

SQL>  col name for a10

SQL>  col db_link for a10

SQL>  col type for a10

SQL>  col kept for a10

SQL> SELECT   owner,

  2             name,

  3             db_link,

  4             TYPE,

  5             locks,

  6             kept

  7      FROM   v$db_object_cache

  8     WHERE   owner = 'ZHAOMN' AND locks != 0

  9  ORDER BY   locks DESC;

 

OWNER      NAME       DB_LINK    TYPE            LOCKS KEPT

---------- ---------- ---------- ---------- ---------- ----------

ZHAOMN     ZHAOMN                PUB_SUB             1 NO

ZHAOMN     A_T1                  PROCEDURE           1 NO