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. 

The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

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

ERROR

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

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>