ORA-04021

A colleague had another problem with a package compilation hanging in an Oracle 11.1.0.6.0 test database. I was able to reproduce it as follows: 

SQL> alter package srce.pk_pricing compile

  2  /

alter package srce.pk_pricing compile

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

 

SQL>

 

There were locks on this package according to V$DB_OBJECT_CACHE but this time, flushing the shared pool made no difference and I found that I still could not compile the package:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4  and name = 'PK_PRICING'

  5  /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL>

 

I read somewhere that you could not compile a package if somebody was using it and you could find who it was by looking in V$ACCESS. I joined it with V$SESSION to pick up the SERIAL# as follows:

 

SQL> l

  1  select a.sid, serial#

  2  from v$access a, v$session b

  3  where a.sid = b.sid

  4* and object = 'PK_PRICING'

SQL> /

 

       SID    SERIAL#

---------- ----------

       187       7623

       225       3111

       179       6987

 

SQL>

 

I killed the first session:

 

SQL> alter system kill session '187,7623'

  2  /

 

System altered.

 

SQL>

 

… and the number of locks in V$DB_OBJECT_CACHE went down:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          2

PACKAGE                               2

 

SQL>

 

I killed the other two sessions:

 

SQL> alter system kill session '225,3111'

  2  /

 

System altered.

 

SQL> alter system kill session '179,6987'

  2  /

 

System altered.

 

SQL>

 

The number of locks in V$DB_OBJECT_CACHE went to zero:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          0

PACKAGE                               0

 

SQL>

 

… and I was able to compile the package in a second or two:

 

SQL> alter package srce.pk_pricing compile

  2  /

 

Package altered.

 

SQL>

If this does not work for you, click on the Older Post link below to see what I did the first time this happened.