Avoid Hanging When Compiling Oracle Package
we have a situation where compiling a package takes forever! if we compile a package with a new name, then it works!
what i figured out, compilation hangs due to package locks!
something like this might help identify the problem!
SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1,
FROM dba_lock_internal l,
v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE '%PROCEDURE_NAME%'
AND l.lock_type = 'Body Definition Lock';
also this
select
x.sid
from
v$session x, v$sqltext y
where
x.sql_address = y.address
and
y.sql_text like '%PROCEDURE_NAME%';
is it only 'body Definition Lock' that prevent compilation? are there any other types of locks that prevent compilation?
how to avoid blocking and compile? killing only sessions? is there something else?
source to share
You might want to look into version based Redeinition, which will allow you to create a new version, compile new versions without blocking other sessions currently using packages, and will later include the new version.
source to share
Basically, if someone or something else (any other scheduled job) is executing the package, then you won't be able to recompile. To get around this, you need to define the blocking session and kill it. Killing session is the option we have, dbms_lock is only useful for locks created by dbms_lock. You can't just "unlock" an object — the lock exists for a very important reason.
Another blocking you may encounter is Dependency Blocking . Procedure-1 from package A contains a call to procedure-2 from package B. procedure-1 from package A has started. Then you can get a lock while compiling package-B
source to share