CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Data-Base
Data-Base

Reputation: 8598

Avoid hanging while compiling Oracle package

we have a situation where the compiling of a package takes for ever! if we compile the package with a new name then it works!

what I understood, Compiling hangs because of locks on the package!

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 the compiling? is there any other lock types that prevent the compiling?

how to avoid the locks and do the compiling? by killing the sessions only? is there something else?

Upvotes: 7

Views: 20581

Answers (2)

Vishal
Vishal

Reputation: 559

Basically, if someone or something else (any other scheduled job) 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. Killing session is that option we have, dbms_lock is only useful on locks created by dbms_lock You cannot just "unlock" some object - the lock is there for an extremely relevant reason.

Other lock you may come across is Dependency Lock: Consider Procedure-1 from Package A contains a call to Procedure-2 from Package B. procedure-1 from Package A is running. Then you may get lock while compiling Package-B

Upvotes: 2

HAL 9000
HAL 9000

Reputation: 3985

You might want to look into Edition-based Redefinition which will let you create a new revision, compile new versions without being blocked by other sessions currently using the packages and enable the new revision later on.

Upvotes: 3

Related Questions