How to recompile a PL/SQL package locked by another user

I’ve hit this problem a couple of times lately where a developer is trying to recompile a PL/SQL package and their session hangs until  they eventually get this error:

SQL> @new_pkg
CREATE OR REPLACE PACKAGE BODY test_package
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

Basically, whilst someone or something else (a scheduled job perhaps?) is executing the package, then you won’t be able to perform the recompile.  To get around this, you need to identify the locking session and kill it.

Executing this script as SYS (or another user with the appropriate privileges) will prompt you for the package name and reveal the culprit(s):

BREAK ON sid ON lock_id1 ON kill_sid

COL sid            FOR 999999
COL lock_type      FOR A38
COL mode_held      FOR A12
COL mode_requested FOR A12
COL lock_id1       FOR A20
COL lock_id2       FOR A20
COL kill_sid       FOR A50

SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

    SID LOCK_TYPE                              MODE_HELD    MODE_REQUEST LOCK_ID1             KILL_SID
------- -------------------------------------- ------------ ------------ -------------------- --------------------------------------------------
    218 Body Definition Lock                   Null         None         USER2.TEST_PACKAGE   alter system kill session '218,12455' immediate;

NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql

Check out what the offending session is doing:

BREAK ON sid ON username ON osuser ON os_pid ON program

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE  s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/

SID   USERNAME     OSUSER       OS_PID     PROGRAM               SQL_TEXT
----- ------------ ------------ ---------- --------------------- --------------------------------------------------------------
218   USER2        oracle       8023       oracle@linux01 (J001) DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;

In this case, it was a background job process which could be killed…

Run the kill session command (kill_cmd) generated from the output in the first script:

SQL> alter system kill session '218,12455' immediate;
System altered.

Now we can recompile the package:

SQL> @new_pkg
Package body created.