EBR – Part 11: Database-Level Default Edition

This is part 11 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series


As we’ve seen in the previous posts, the process of an online application upgrade, in high level, includes the following steps:

  • Creating a new edition
  • Applying all the necessary changes in the privacy of the new, unexposed, edition
  • Making sure that all the objects in the new edition are valid, and that all the objects that should be actual in the new edition are indeed actual
  • Exposing the new edition to the clients

How do we expose the new edition?

In the previous examples I did it by explicitly changing the client’s session edition, from the client session itself. But that was just for demo purposes, and it is certainly not the recommended way.
The best way to expose new editions, in my opinion, is by using services. This gives us high levels of both flexibility and control.
I’ll dedicate the next post for exposing new editions via services.
In this post I’ll show another option – Database Default Edition. This method is very simple, but also very limiting, and therefore it’s suitable only for very specific cases.

Database Default Edition

There is always one database-level default edition. We can see it using this query:

select property_value
from   database_properties
where  property_name = 'DEFAULT_EDITION';


By default, when we open a session it is associated with the database-level default edition.
So we can expose the new edition by setting it as the database-level default edition, and from that point on, every new session will use the new edition.

Let’s see an example – I’ll repeat the first online upgrade that I demonstrated in part 3.

We have a client session, that is working with the initial implementation, using the ORA$BASE edition (which is currently the default edition, and the only one in the database).

-- client session #1
ORA$BASE> conn demo_ebr/ebr

Let’s now create the new edition V1:

ORA$BASE> create edition V1;

Edition created.

ORA$BASE> grant USE on edition V1 to demo_ebr;

Grant succeeded.

Let’s move to edition V1 in the developer session:

-- the developer session
ORA$BASE> conn demo_ebr/ebr

ORA$BASE> @set_edition V1

Session altered.


And let’s compile the new version of the PEOPLE_DL package body:

V1> create or replace package body people_dl as
  2      procedure add
  3      (
  4          i_id           in people.id%type,
  5          i_first_name   in people.first_name%type,
  6          i_last_name    in people.last_name%type,
  7          i_phone_number in people.phone_number%type
  8      ) is
  9      begin
 10          insert into people (id,first_name,last_name,phone_number)
 11          values (i_id,i_first_name,i_last_name,i_phone_number);
 12          --
 13          dbms_output.new_line;
 14          dbms_output.put_line('wow! this is not the base edition anymore');
 15          --
 16      end add;
 18      procedure remove(i_id in people.id%type) is
 19      begin
 20          delete people where id = i_id;
 21      end remove;
 22  end people_dl;
 23  /

Package body created.

Now, let’s expose the new edition V1, by making it the database default edition:

ORA$BASE> alter database default edition = V1;

Database altered.

So when we open a new client session, the session edition is automatically V1:

-- client session #2
C:\> sqlplus demo_ebr/ebr

And we can see that it is using the new implementation of people_dl:

-- client session #2
V1> exec app_mgr.do_something

wow! this is not the base edition anymore

PL/SQL procedure successfully completed.

The first client session had been opened before we exposed the new edition, so it is still using the pre-upgrade edition, and if we execute the same procedure from that session we see it’s indeed using the old implementation:

-- client session #1
ORA$BASE> exec app_mgr.do_something

person added using the BASE version

PL/SQL procedure successfully completed.

This is the beauty of hot rollover! We have two sessions, connected to the same schema, executing the same procedure, and yet, they are executing different implementations.

Although exposing new editions using the database-level default edition is very simple, it is, as I mentioned above, very limiting.
By definition, there is only one default edition for the whole database, so if we have several applications in the same database, they are all affected by the change of the default edition at the same time.
And the biggest downside, in my opinion, is that we decide for the client that from now on it will use the new edition. If in the new edition we make API changes (that are not backward compatible), then the client-side must be upgraded at the exact same time as the change of the default edition. This is usually impractical.

Services solve these issues, and this is what we’ll see in the next post.

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

Leave a Reply

Your email address will not be published.