EBR – Part 2: Locking, Blocking and ORA-04068

This is part 2 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In this post we’ll start handling the first type of change request: changing a package body.

Visit the index page for all the parts of the series

The Task

We need to change the implementation of the PEOPLE_DL package; i.e. we need to change the package body.
There are no API changes (the package spec is not changed) and no table changes.
And of course, as we speak about EBR, the upgrade from the base version to the new one should be online.

The Problems

Locking and Blocking

An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
One of the many advantages of using PL/SQL packages is that a package body has no dependent objects. It means that compiling a package body never invalidates other code objects.
So what is the challenge in changing a package body in an online upgrade? Why can’t we just compile the package body?
One problem is that in order to compile the package body we have to exclusively lock it – to make sure no other session is using it while it’s being compiled. It means that:

  • The compilation is blocked by any other session that is currently using the package
  • and

  • As soon as the compiling session starts waiting to lock the package, and until the compilation is done, it blocks any other session that wants to use the package

In a busy system, this can lead to a snowball of locks, and the upgrade becomes an offline one (and of the worst kind – an unplanned offline upgrade).

We can easily simulate this.
Let’s create a package pkg with a single procedure p that does… nothing.

create or replace package pkg as
    procedure p;
end pkg;
create or replace package body pkg as
    g_var number := 42;
    procedure p is
    end p;
end pkg;

And let’s create two standalone procedures: test_execute that calls pkg.p and then sleeps for 10 seconds; and test_compile that simply compiles the pkg package body:

create or replace procedure test_execute as
end test_execute;
create or replace procedure test_compile as
    execute immediate 'alter package pkg compile body';
end test_compile;

Now I’ll schedule 8 jobs; each one starts 1 second after the previous one has started. All of them represent end-user sessions, except for the third one, which represents the developer session. Each of the end-user sessions calls the test_execute procedure, and the developer session calls the test_compile procedure.

    v_base_time date := sysdate;
    for i in 1 .. 8
          job_name   => '"session#' || i || ' (' || 
                        case i when 3 then 'developer' else 'end user' end ||
          job_type   => 'STORED_PROCEDURE',
          job_action => case i when 3 then 'test_compile' else 'test_execute' end,
          start_date => v_base_time + numtodsinterval(i, 'second'),
          enabled    => true);
    end loop;

The following query shows library cache pins and locks held and requested by the various sessions:

col action format a21
col event format a20
col blocker format a21
col object_type format a20
col object_name format a20
col mode_held format a10
col mode_requested format a10

break on action on event on blocker on blocked_duration skip 1

select s.action,
       b.action blocker,
       nvl2(b.sid,s.seconds_in_wait,null) blocked_duration,
       o.kglobtyd object_type,
       o.kglnaobj object_name,
       decode(l.mode_held, 1, 'null', 2, 'share', 3, 'exclusive') mode_held,
       decode(l.mode_requested, 1, 'null', 2, 'share', 3, 'exclusive') mode_requested
from   v$session        s,
       v$libcache_locks l,
       x$kglob          o,
       v$session        b
where  s.saddr = l.holding_user_session
and    o.kglhdadr = l.object_handle
and    o.kglnaown = 'DEMO5'
and    (l.mode_held in (2, 3) or l.mode_requested in (2, 3))
and    b.sid(+) = s.blocking_session
order  by action,

I executed this query about 2 seconds after the last job has started:

ACTION                EVENT                BLOCKER               BLOCKED_DURATION OBJECT_TYPE          OBJECT_NAME          TYPE       MODE_HELD  MODE_REQUE
--------------------- -------------------- --------------------- ---------------- -------------------- -------------------- ---------- ---------- ----------
session#1 (end user)  PL/SQL lock timer                                           PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN        share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#2 (end user)  PL/SQL lock timer                                           PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN        share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#3 (developer) library cache pin    session#2 (end user)                 7 PACKAGE BODY         PKG                  LOCK       exclusive
                                                                                  PACKAGE BODY         PKG                  PIN                   exclusive
                                                                                  PROCEDURE            TEST_COMPILE         PIN        share

session#4 (end user)  library cache pin    session#3 (developer)                6 PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN                   share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#5 (end user)  library cache pin    session#3 (developer)                5 PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN                   share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#6 (end user)  library cache pin    session#3 (developer)                4 PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN                   share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#7 (end user)  library cache pin    session#3 (developer)                3 PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN                   share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

session#8 (end user)  library cache pin    session#3 (developer)                2 PACKAGE              PKG                  PIN        share
                                                                                  PACKAGE BODY         PKG                  PIN                   share
                                                                                  PROCEDURE            TEST_EXECUTE         PIN        share

24 rows selected.

We can see that in order to execute objects, the (end-user) sessions have to hold a library cache pin on them in share mode. And for compiling an object, the (developer) session has to hold the pin in exclusive mode.

Note that the library cache pins on the pkg package spec and body are held not only during the time when the package is explicitly used, but since its use until the end of the top level call in which it is included.

Since I used jobs for scheduling the test, we can now query user_scheduler_job_run_details to see when each session started and ended:

col job_name format a21
col start_time format a10
col end_time format a10
col progress format a25

select d.job_name,
       to_char(d.actual_start_date, 'hh24:mi:ss') start_time,
       to_char(d.actual_start_date + d.run_duration, 'hh24:mi:ss') end_time,
       lpad(' ', round(extract(second from d.actual_start_date - min(d.actual_start_date) over()))) ||
       lpad('*', extract(second from d.run_duration), '*') progress
from   user_scheduler_job_run_details d
where  d.job_name like 'session%'
order  by start_time

--------------------- ---------- ---------- -------------------------
session#1 (end user)  09:03:12   09:03:22   **********
session#2 (end user)  09:03:13   09:03:23    **********
session#3 (developer) 09:03:14   09:03:23     *********
session#4 (end user)  09:03:15   09:03:33      ******************
session#5 (end user)  09:03:16   09:03:33       *****************
session#6 (end user)  09:03:17   09:03:33        ****************
session#7 (end user)  09:03:18   09:03:33         ***************
session#8 (end user)  09:03:19   09:03:33          **************

8 rows selected.

Note: I colored in red the time in which each session was blocked.

To summarize the test results:

  • The end-user sessions 1 and 2 were executed concurrently
  • The developer session was being blocked until end-user sessions 1 and 2 were done
  • All the other end-user sessions (4 through 8) were being blocked by the developer session, and all of them were released and were running concurrently once the developer session was done


There is another potential problem with compiling a package body during an online upgrade. If the package body includes global variables, and a session has used the package before the upgrade, then if the same session tries to use the package after the upgrade it will get the error “ORA-04068: existing state of packages has been discarded”.
Let’s see it…
Note that the pkg package body includes a global variable g_var.
From one session we’ll execute pkg.p:

exec pkg.p

PL/SQL procedure successfully completed.

From a second session we’ll compile the package body:

alter package pkg compile body;

Package body altered.

And then we’ll call pkg.p again from the first session:

exec pkg.p
BEGIN pkg.p; END;

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "DEMO5.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "DEMO5.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "DEMO5.PKG"
ORA-06512: at line 1

The Solution

We’ve seen that even a simple change, that requires only a single package body compilation, may cause severe problems if it’s carelessly applied to an active system.
So how can we apply such a change in both online and safe way? As we’ll see in the next part of this series, we can do it easily, using editions.

For other parts of the EBR series, please visit the index page


  1. Foued

    Thanks for the post.

  2. Dillip B

    Very informative

Leave a Reply

Your email address will not be published.