存储过程无法删除

郑全

一.问题现象:

    一用户昨晚来电称,做了一个存储过程,过程如下:

CREATE OR REPLACE PROCEDURE REPORT_OLD_USER_DETAIL_02(v_period number) IS
  TYPE mobileno_table_type IS TABLE OF report_month_user.mobileno%type;
  mobileno_table_this_month mobileno_table_type;
  mobileno_table_last_month mobileno_table_type;
  mobileno_table_result mobileno_table_type;
  TYPE c1 IS REF CURSOR;
  v_mobileno_cursor_this c1;
  v_mobileno_cursor_last c1;
BEGIN
  OPEN v_mobileno_cursor_this FOR
    SELECT mobileno FROM report_month_user WHERE period = v_period;
  OPEN v_mobileno_cursor_last FOR
    SELECT mobileno FROM report_month_user WHERE period = v_period - 1;

  FETCH v_mobileno_cursor_this BULK COLLECT
    INTO mobileno_table_this_month;
  FETCH v_mobileno_cursor_last BULK COLLECT
    INTO mobileno_table_last_month;

  mobileno_table_result := mobileno_table_this_month MULTISET INTERSECT mobileno_table_last_month;

  FOR i IN 1 .. mobileno_table_result.count LOOP
    INSERT INTO REPORT_MONTH_OLD_USER
      (mobileno, area)
    VALUES
      (mobileno_table_result(i), getareaname(mobileno_table_result(i)));
  END LOOP;

  CLOSE v_mobileno_cursor_this;
  CLOSE v_mobileno_cursor_last;
END;

  该过程执行一段时间后,发现还没有执行结束,就直接退出了,后来对该过程重新编译,一编译,就卡在那里,如果对该过程进行删除,

   比如:

   drop procedure REPORT_OLD_USER_DETAIL_02;

   也卡在那里.

 

二.解决过程

   1.重复现象,确认问题确实存在

   2.通过plsql/developer 的tools/session查看,没有其他的进程访问该对象

   3.在卡死的过程中,检查v$session 的blocking_session,没有发现有阻塞的session存在

   4.检查v$lock,没有发现tm,tx的锁存在

   5.检查v$locked_object,也没有对象锁的存在

   6.检查v$session_wait,到是发现了问题,在卡死的过程中,发现该会话在等待library cache pin

      看来问题在这里,应该是该过程无法进行pin导致

   7.再检查v$session,还是没有发现问题.

       范了一个错误,始终使用plsql/developer的工具/session查看,该工具,对一些系统进程和特殊进程不显示.

   8.既然在library cache中存在问题,对共享池冲洗一次应该有效

       alter system flush shared_pool;

       但执行以上操作后,问题依旧.

   9.检查V$DB_OBJECT_CACHE ,发现lock=1

      SELECT * FROM V$DB_OBJECT_CACHE WHERE name=REPORT_OLD_USER_DETAIL_02' AND LOCKS!='0';

      看来确实有session还在锁住该对象

   10.检查v$access,发现确实存在一个session

      select SID from V$ACCESS WHERE object='REPORT_OLD_USER_DETAIL_02';

      ----

      1611

   11.再次执行v$session

      select sid,username,status,LOGON_TIME from v$session where sid=1611   ;

      发现时间正好是第一次退出该过程的时间,并且状态为killed.

      看来问题就在这里了.

    12.杀该进程

      由于该session已经处于killed状态,通过v$process无法找到操作系统进程,因此需要使用另外的方式,如下:

        SELECT 
     'kill -9 ' || P.SPID
    FROM V$PROCESS P
    WHERE NOT EXISTS (SELECT 'X' FROM 
      V$SESSION WHERE PADDR = P.ADDR AND STATUS <> 'KILLED')
      AND P.PID <> 1;

 

      但需要核对一下,该进程是否为用户进程

      ps -ef|grep spid  --这里的spid为上面找出的spid

      如果为LOCAL=NO的进程,就可以杀

    13.杀掉该进程后,再去检查v$access,V$DB_OBJECT_CACHE ,没有记录

    14.再去删除过程REPORT_OLD_USER_DETAIL_02'

      成功执行.

    至此问题解决.

 

    总结一下,这个问题的根源,就是中途异常退出过程,导致该进程始终在library cache中锁住所导致,杀掉即可.

 

 

    附录:

    1.v$db_object_cache

V$DB_OBJECT_CACHE

V$DB_OBJECT_CACHE displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

Column	Datatype	Description
OWNER	VARCHAR2(64)	Owner of the object
NAME	VARCHAR2(1000)	Name of the object
DB_LINK	VARCHAR2(64)	Database link name, if any
NAMESPACE	VARCHAR2(64)	Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT
TYPE	VARCHAR2(64)	Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK
SHARABLE_MEM	NUMBER	Amount of sharable memory in the shared pool consumed by the object
LOADS	NUMBER	Number of times the object has been loaded. This count also increases when an object has been invalidated.
EXECUTIONS	NUMBER	Not used See Also: "V$SQLAREA" to see actual execution counts
LOCKS	NUMBER	Number of users currently locking this object
PINS	NUMBER	Number of users currently pinning this object
KEPT	VARCHAR2(3)	(YES | NO) Depends on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP
CHILD_LATCH	NUMBER	Child latch number that is protecting the object. This column is obsolete and maintained for backward compatibility.
INVALIDATIONS	NUMBER	Total number of times objects in the namespace were marked invalid because a dependent object was modified

 

  

2.v$access

V$ACCESS

V$ACCESS displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out of the library cache while they are required for SQL execution.

Column	Datatype	Description
SID	NUMBER	Session number that is accessing an object
OWNER	VARCHAR2(64)	Owner of the object
OBJECT	VARCHAR2(1000)	Name of the object
TYPE	VARCHAR2(64)	Type identifier for the object