Locked Package (ORA-04021)

When releasing a package to test, I got the following error. That is after a significant wait time.

Calling "pkg_loss _spec.sql"...

CREATE OR REPLACE PACKAGE pkg_loss IS

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

 

So I checked for blocking locks but that doesn’t apply to DDL. Instead you need to check the V$ACCESS view.

SELECT * FROM v$access WHERE object = 'PKG_LOSS';

 

This shows that there is certainly some sessions holding the object. So having discussed the matter with the developer, they authorised me to kill the sessions holding the object. So put this next statement together. It shows the relevant session information, plus the kill statement.

 

select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,

      'alter system kill session '''||sid||','||serial#||''' immediate;' comm

from v$session sess where sid in (

SELECT sid FROM v$access WHERE lower(object) = 'pkg_rege_loss_objection');

 

I then copy & paste the kill statements I needed, and then was able to replace the package.

 

alter system kill session '230,2911' immediate;

 

 

HappyJohn.