When developer is trying to recompile PL/SQL package session hangs until get this error:
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):
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' /
NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql
check what is doing this session
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 /
run kill command from the output of first script
alter system kill session 'SID,SERIAL#' immediate;
after that developer can recompile package.