Procedure is not getting complied

If the package or procedure is not getting complied, then one of the reasons why the package XX_YY_ZZ is not getting compiled is there are few session locks which got applied. We can find them using below query, then release them and try compiling the package…

Query ran:

SELECT *

FROM v$session

WHERE SID IN (SELECT UNIQUE SID

FROM v$access

WHERE OBJECT='&OBJECT_NAME');

OR you may use below query and output of below query will give ready made statements to kill these locks and you can directly use it…

SELECT 'alter system kill session '''||SID||','||serial#||''';'

FROM v$session

WHERE SID IN (SELECT UNIQUE SID

FROM v$access

WHERE OBJECT='&OBJECT_NAME');

Example:

alter system kill session '1120,27788';

alter system kill session '1187,32246';

alter system kill session '1355,64';

alter system kill session '1060,11503';

alter system kill session '1314,21';

We already used this solution in one of the project. After running above statements, we could able to complete the package within few seconds.