This is an old revision of the document!

Check which session is locking a package

Replace the PACKAGE_NAME with the real name of the package.

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'

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;