====== Check which session is locking a package ======

Replace the PACKAGE_NAME with the real name of the package.

<code sql>
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'
</code>

Output:

^ SID  ^ LOCK_TYPE            ^ MODE_HELD ^ MODE_REQUESTED ^ LOCK_ID1           ^ KILL_SID ^
| 164  | Body Definition Lock | Null      | None           | USER1.PACKAGE_NAME | alter system kill session '164,64447' immediate |

====== Check details about the locking session ======

Replace 164 with the actual sid:

<code sql>
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 = 164
ORDER BY s.sid, t.hash_value, t.piece
</code>

The above code may return no results if the sqltext cannot be matched. \\
In this case, try with the following query:

<code sql>
SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program
FROM   v$session s,
       v$process p
WHERE  s.sid = 164
AND    s.paddr = p.addr
</code>