How to recompile a PL/SQL package if it is used by another session?
Imagine you have to recompile a PL/SQL package urgently. In case it is being used and other sessions are executing it , you will not be able to compile this package successfully.
What you get is :
ORA-04021: timeout occurred while waiting to lock object error
The solution is to find out the locking session and to kill it. Let me say you need to change PCK_TEST package, then:
select * from dba_lock_internal
where lock_id1 like ‘%PCK_TEST%’;
Get the session_id and if possible for the system kill that session in order to compile the package.