Problem
SQL> ALTER PACKAGE TOM.REMOTE_DBA COMPILE PACKAGE;
ALTER PACKAGE TOM.REMOTE_DBA COMPILE PACKAGE
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object TOM.REMOTE_DBA
Solution
SQL> BREAK ON sid ON lock_id1 ON kill_sid
SQL>
SQL> COL sid FOR 999999
SQL> COL lock_type FOR A38
SQL> COL mode_held FOR A12
SQL> COL mode_requested FOR A12
SQL> COL lock_id1 FOR A20
SQL> COL lock_id2 FOR A20
SQL> COL kill_sid FOR A50
SQL>
SQL> SELECT s.sid,
2 l.lock_type,
3 l.mode_held,
4 l.mode_requested,
5 l.lock_id1,
6 ‚alter system kill session “’|| s.sid|| ‚,’|| s.serial#|| “‘ immediate;‘ kill_sid
7 FROM dba_lock_internal l,
8 v$session s
9 WHERE s.sid = l.session_id
10 AND UPPER(l.lock_id1) LIKE ‚%&package_name%‘
11 AND l.lock_type = ‚Body Definition Lock‘
12 /
Enter value for package_name: TOM.REMOTE_DBA
old 10: AND UPPER(l.lock_id1) LIKE ‚%&package_name%‘
new 10: AND UPPER(l.lock_id1) LIKE ‚%TOM.REMOTE_DBA%‘
SID LOCK_TYPE MODE_HELD MODE_REQUEST
——- ————————————– ———— ————
LOCK_ID1 KILL_SID
——————– ————————————————–
1236 Body Definition Lock Null None
TOM.REMOTE_DBA alter system kill session ‚1236,12366‘ immediate;
1203 Body Definition Lock Null None
TOM.REMOTE_DBA alter system kill session ‚1203,39028‘ immediate;
767 Body Definition Lock Null None
TOM.REMOTE_DBA alter system kill session ‚767,46943‘ immediate;
Source: https://snapdba.com/2013/03/how-to-recompile-a-plsql-package-locked-by-another-user/