Oracle package compile hangs problem

How to Determine if a Package that is About to be Compiled is Being Used Currently:

When you try to compile an oracle package or procedure, it will hang if it is used by another session. You have to find the session that uses this package and kill it. 

For this purpose, you need to create a package called "who_is_using". To download this oracle package log on to MOS, navigate to doc 1054939.6. 

After creating this package login to sqlplus as sysdba:

$ sqlplus /nolog

SQL> connect / as sysdba

SQL> set serveroutput on

SQL> exec who_is_using('my_funct%');

(1-198) - WEBUSER

PL/SQL procedure successfully completed.



You should replace ('my_funct%') with your package name. 

After finding the session, which uses this package, you can kill it or wait for it to finish.


If the above query returns null than you can succesfully compile your procedure or package.