Compile blocked package/procedure/function (Part II)

In one of mine previous post (Compile blocked package/procedure/function) I have shown how to determine who is blocking compiling of some package, procedure or function.

After I get several mails and saw that this approach is still "fog and pain", I have decided to show how this could be fully automated.

The implementation

CREATE OR REPLACE procedure kill_compile_blocker (
/* ---------------------------------------------------------------------------

 Copyright(C) 1995-2010 TEB Informatika d.o.o.


 Filename: kill_compile_blocker.sql (AKA check_blocking_objects)
 CR/TR#  : 
 Purpose : Show blocking session on non "table" objects
           Session which prevent compile of some prc/fnc/package etc.
           
           If passed, p_waiter_sid, is SID that is waiter! All result is then 
           based only on this one session. 
           
 Date    : 13.01.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g/11g 
           No RAC support
           
 Changes (DD.MM.YYYY, Name, CR/TR#):
          23.09.2010  Damir Vadas
                      consolidated code
--------------------------------------------------------------------------- */
                                                   p_waiter_sid number DEFAULT null) 
AS
  l_waiter_sid         NUMBER;
  l_waiter_lock_addr   VARCHAR (40);
  stmt                 VARCHAR2(256);
  l_inst_id            number;
  
   -- list of all blockers that holds our proc/fnc/pkg
  CURSOR cur_blockers (pc_waiter_sid NUMBER, pc_lock_id  VARCHAR2)
  IS
    SELECT SESSION_ID blocker_sid, 
           MODE_HELD,    
           LOCK_ID1   locked_object
      FROM dba_lock_internal
     WHERE mode_held NOT IN ('None', 'Null')
       AND lock_id2 = pc_lock_id
       AND SESSION_ID != pc_waiter_sid
    -- ORDER BY SESSION_ID
  ;
  
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
BEGIN
  dbms_output.put_line (chr(9));
  dbms_utility.db_version(db_ver,db_ver2);
  dbms_output.put_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  dbms_output.put_line (chr(9));

  IF p_waiter_sid is null THEN
    -- find first blocked sid and release him! 
    SELECT DISTINCT 
           SESSION_ID waiter_sid, 
           lock_id2 waiter_lock_addr
       INTO l_waiter_sid, l_waiter_lock_addr
       FROM dba_lock_internal
      WHERE mode_requested = 'Exclusive'
        AND mode_requested <> mode_held
        AND LOCK_TYPE ='Table/Procedure/Type Definition Pin'
    ORDER BY 1 DESC
    ;
  ELSE
    -- find exact blocked sid
    SELECT SESSION_ID, lock_id2
      INTO l_waiter_sid, l_waiter_lock_addr
      FROM dba_lock_internal
     WHERE SESSION_ID = p_waiter_sid
       AND mode_requested = 'Exclusive'
       AND mode_requested <> mode_held
       AND LOCK_TYPE ='Table/Procedure/Type Definition Pin'
    ;
  END IF;
  stmt := null;
  FOR i IN cur_blockers (l_waiter_sid,l_waiter_lock_addr) LOOP
    dbms_output.put_line('-------------------------------------');
    SELECT    'alter system disconnect session '''
           || s.sid
           || ','
           || s.serial#
           || ''' immediate ;'
      INTO stmt
      FROM v$session s
     WHERE s.sid = i.blocker_sid 
       AND s.serial# != 1
    ;
    dbms_output.put_line('Blocked object:'||i.locked_object || ' ('||i.MODE_HELD||')');
    DBMS_OUTPUT.put_line(chr(9)||'On current instance perform:');
    DBMS_OUTPUT.put_line(chr(9)|| chr(9)|| stmt);
  END LOOP; 
  dbms_output.put_line('-------------------------------------');
EXCEPTION
  WHEN no_data_found then
    dbms_output.put_line('No blocking session found!');
END;
/

As you can see procedure has one parameter p_waiter_sid. This parameter (if defined) determine session_id that is blocked (waiting for compile).
If you pass no parameter, then procedure find first blocked session_id as use it as it is defined as paraemter.
Procedure results are "kill session" statements for ALL sessions that are blocking identified session_id.

Procedure works only for wait events Table/Procedure/Type Definition Pin type and it will not disconnect any other blocking session. If you want to unblock that "other kind of block", explanation is on previous blog Blocking session ... detect, unblock, notify.

Real example

Let us suppose that two session are using package tools_pkg:

User try to compile package tools_pkg and realize he/she is blocked. Run procedure and execute it's output :

Result on previous two session is obvious:

"Compile" session is unblocked and package is compiled.

In this example RAC instances are not covered...a challenge for someone of you ... or me when I get some free time.

Cheers!