Thursday, March 07, 2013

V$DB_OBJECT_CACHE

This happened on Oracle 11.1.0.6.0. A colleague was debugging a package in a test database and his debug session fell over. Subsequent attempts to compile the package timed out as shown below:
 
SQL> alter package srce.pk_deemed_offer compile;
alter package srce.pk_deemed_offer compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
 
SQL>
 
The DBA_BLOCKERS view produced no output while this request was waiting:
 
SQL> select * from dba_blockers;
 
no rows selected
 
SQL>
 
… and there was nothing in V$LOCK either:
 
SQL> select * from v$lock where block = 1;
 
no rows selected
 
SQL>
 
… but V$DB_OBJECT_CACHE showed the following:
 
SQL> l
  1  select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4* and name = 'PK_DEEMED_OFFER'
SQL> /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          2
PACKAGE                               2
 
SQL>
 
I’m not sure if this was the right thing to do but I flushed the shared pool:
 
SQL> alter system flush shared_pool;
 
System altered.
 
SQL>
 
The locks shown in V$DB_OBJECT_CACHE disappeared:
 
SQL> l
  1  select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4* and name = 'PK_DEEMED_OFFER'
SQL> /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          0
PACKAGE                               0
 
SQL>
 
… and the compilation finished OK:
 
SQL> l
  1* alter package srce.pk_deemed_offer compile
SQL> /
 
Package altered.
 
SQL>

If this doesn't work for you, click on the Newer Post link below to see what I did the next time this happened.

No comments: