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!
