Wednesday, March 13, 2013

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. 

No comments: