This is part 3 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 part 2 we saw that even a simple change – such as a package body compilation – can be dangerous in a busy system.
In this post we’ll see how EBR solves the problems described in the previous post, so such a change can be applied in both online and safe way.

Visit the index page for all the parts of the series

Overview

The basic concept of EBR is that while the clients are connected to the database and use the objects – say the package pkg from the previous post – we can create another instance of pkg – a copy of pkg – and make all the necessary changes in this new instance.
We create this new pkg in the same schema, so now we actually have two packages named pkg in one schema. But each session sees only one of these packages – the clients continue seeing the old instance of pkg, and we the developers currently see the new instance of pkg.
When we are happy with the new implementation, we can expose it, so clients that will open new connections to the database from now on, will see the new pkg.
We achieve this by editions. Imagine that our schema is divided into multiple separate areas. At any point in time, each session sees one and only one area.
These separate areas are called editions, and we can keep basically any code object there – procedures, functions, packages, triggers, views, and more.
We can’t have two objects named pkg in the same edition, but we can in two separate editions.

Editions

Editions are database-level objects (like roles and directories), and not schema-level ones (like tables and packages).
There must be at least one edition in the database. The initial edition is ORA$BASE.

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';

PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE

Every new edition is created as a child of an existing edition.
And each edition may have at most one child edition. In other words, we cannot have a tree of editions; only a (single) branch of editions. Some consequences of this “having one and only one branch of editions” rule are:

• A new edition may be created only as the child of the leaf edition
• Only the root edition or the leaf edition may be dropped

Currently, in my demo database, I have only one edition – the default ORA$BASE:

select * from all_editions;

EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ---------
ORA$BASE                         YES

Enabling Editions

Users must be enabled for editions in order to own editioned objects. This is a one-time operation, and it’s irreversible.
It is also a retroactive operation – it affects existing objects and not only future ones. Let’s see it.
In part 1 I used the user DEMO_EBR for creating several objects. We can see that DEMO_EBR is not enabled yet, and that none of its objects are editioned:

select username, editions_enabled
from   dba_users
where  username = 'DEMO_EBR';

USERNAME             EDITIONS_ENABLED
-------------------- --------------------
DEMO_EBR             N

select object_name,object_type,status, edition_name
from   dba_objects
where  owner = 'DEMO_EBR'
order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID
APP_MGR      PACKAGE BODY VALID
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID
PEOPLE_DL    PACKAGE BODY VALID
PEOPLE_PK    INDEX        VALID

6 rows selected.

Let’s enable DEMO_EBR now for editions:

alter user demo_ebr enable editions;

User altered.

And let’s repeat the previous queries:

select username, editions_enabled
from   dba_users
where  username = 'DEMO_EBR';

USERNAME             EDITIONS_ENABLED
-------------------- --------------------
DEMO_EBR             Y

select object_name,object_type,status, edition_name
from   dba_objects
where  owner = 'DEMO_EBR'
order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID   ORA$BASE
APP_MGR      PACKAGE BODY VALID   ORA$BASE
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID   ORA$BASE
PEOPLE_DL    PACKAGE BODY VALID   ORA$BASE
PEOPLE_PK    INDEX        VALID

6 rows selected.

We can see that some of the objects are now editioned. The package specs and package bodies, which are editionable object types, are now associated with a specific edition – ORA$BASE – which is the default (and only) edition in my database at the moment. Tables and indexes are not editionable object types, and therefore the PEOPLE table and its index are not editioned.

Note: I used the default syntax for enabling editions. I’ll discuss advanced (and sometimes risky) options in a later post.

Creating Editions

The packages that we’ve created in part 1 – the initial version of our demo system – exist now in the context of the default edition – ORA$BASE.
Our current task is to change the PEOPLE_DL package body in an online way. For that we’ll create our first non-default edition. Let’s call it V1:

create edition V1;

Edition created.

As simple as that!

We could use the full syntax:

create edition V1 as child of ora$base;

but since a new edition can be created only as the child of the current leaf edition, it doesn’t really have any benefits (except for, perhaps, clarity).
If we do try to create another edition as a child of ORA$BASE, which is no longer the leaf edition, we’ll get this exception:

create edition doomed_to_fail as child of ora$base;
create edition doomed_to_fail as child of ora$base
*
ERROR at line 1:
ORA-38807: Implementation restriction: an edition can have only one child

So now we have two editions in the database…

select *
from dba_editions;

EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ---------
ORA$BASE                         YES
V1           ORA$BASE            YES

… ORA$BASE, the default edition that our clients are currently using; and V1, that for now only we, the developers, know about, and actually nobody can use yet.

Using Editions

In order to use a specific edition, users must be granted the USE privilege on the edition.
Let’s grant our schema owner – DEMO_EBR – the USE privilege on the new edition, V1:

grant USE on edition V1 to demo_ebr;

Grant succeeded.

Note that PUBLIC is always granted the database default edition.

Session Edition

Each session uses one specific edition at any point in time. By default, when we open a session it is associated with the database-level default edition.

There are various ways to set or change the session edition. We’ll see one way shortly, and all the others in future posts.

We can see our session edition by looking at the value of the SESSION_EDITION_NAME parameter of the USERENV context:

select sys_context('USERENV','SESSION_EDITION_NAME') from dual;

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')
----------------------------------------------------------------------------------------------------
ORA$BASE

In SQL*Plus we can use:

show edition

EDITION
------------------------------
ORA$BASE

We can see the session edition of every session in the instance, using the SESSION_EDITION_ID column of V$SESSION. For example:

select s.sid,
       s.serial#,
       s.username,
       o.object_name edition_name
from   v$session   s,
       all_objects o
where  s.type = 'user'
and    s.session_edition_id = o.object_id;

   SID    SERIAL# USERNAME             EDITION_NAME
------ ---------- -------------------- ------------
   240      30181 OREN                 ORA$BASE
   299      21680 SYSTEM               ORA$BASE
   302      17851 SYSTEM               ORA$BASE

So even if you don’t use EBR, never created an edition or enabled users for editions, and you’re on Oracle 11.2 or above, every time you open a session you actually use editions – your session is using some edition (most probably the default one – ORA$BASE).

Editioned Objects

Every editioned object in the context of a specific edition is either actual or inherited.
Right after the creation of a new edition it inherits all the editioned objects of its parent edition, and has no actual objects. Once an object is compiled in the new edition it is no longer inherited and becomes actual.
In our example, the 4 code objects – the PEOPLE_DL and APP_MGR package specs and package bodies – are actual in ORA$BASE. At this point we’ve only created V1 and did not compile any object in it, so all of these objects are inherited in V1.

By definition, when we are in the context of a specific edition, we see and use the objects that are actual in this session and the inherited objects from ancestor editions, but never objects from descendant editions.
This observation may seem trivial, but in my opinion it’s the cornerstone of EBR. This is what lets us apply the new or changed code in the privacy of a new edition, where no client is exposed to the new version until we want to expose it.

The Online Upgrade

Let’s start a session that represents our client and another session for us the developers. I’ll use blue text for the client and magenta for the developer.
For now, just for simplicity, both the developer and the client will connect as DEMO_EBR – the schema owner.

-- the client session
ORA$BASE> conn demo_ebr/ebr
Connected.

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

The client is working with the initial implementation, using the ORA$BASE edition.

ORA$BASE> exec app_mgr.do_something

person added using the BASE version

PL/SQL procedure successfully completed.

While the clients are working with the base version, we (the developer) will compile the new version of the PEOPLE_DL package body in the new edition V1. For that we should change our session edition to V1, and we can do it like that:

alter session set edition = V1;

I like using a small script – set_edition.sql – that sets the session edition and changes the prompt to reflect the up-to-date session edition:

--
-- set_edition.sql
--
alter session set edition = &1;

set termout off
col edition new_value edition
select sys_context('userenv','session_edition_name') edition from dual;
set sqlprompt '&edition> '
set termout on

So let’s move to edition V1 in the developer session:

ORA$BASE> @set_edition V1

Session altered.

V1>

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;
 17
 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 take a look at USER_OBJECTS:

V1>  select object_id,
  2         object_name,
  3         object_type,
  4         last_ddl_time,
  5         status,
  6         edition_name
  7  from   user_objects
  8  where  object_type like 'PACK%'
  9  order  by last_ddl_time;

 OBJECT_ID OBJECT_NAME  OBJECT_TYPE  LAST_DDL_TIME      STATUS  EDITION_NAME
---------- ------------ ------------ ------------------ ------- ------------
    119435 PEOPLE_DL    PACKAGE      01-DEC-17 20:03:19 VALID   ORA$BASE
    119437 APP_MGR      PACKAGE      01-DEC-17 20:03:24 VALID   ORA$BASE
    119438 APP_MGR      PACKAGE BODY 01-DEC-17 20:03:24 VALID   ORA$BASE
    120794 PEOPLE_DL    PACKAGE BODY 15-DEC-17 11:29:53 VALID   V1

We can see that the EDITION_NAME column for the PEOPLE_DL package body is V1 and for the other objects is ORA$BASE. It means that there is an actual instance of the PEOPLE_DL package body in V1 (as we’ve just compiled it in this edition), and that the other three objects are not actual in V1 – they are inherited and we see their actual instances from ORA$BASE.

When we call or refer to some editioned object, it will always be one specific instance of this object, based on our current edition. The USER_OBJECTS view reflects exactly that. But there is another view – USER_OBJECTS_AE – which allows us to see all the actual instances of the objects (“AE” stands for “All Editions”). Let’s query this view:

V1>  select object_id,
  2         object_name,
  3         object_type,
  4         last_ddl_time,
  5         status,
  6         edition_name
  7  from   user_objects_ae
  8  where  object_type like 'PACK%'
  9  order  by last_ddl_time;

 OBJECT_ID OBJECT_NAME  OBJECT_TYPE  LAST_DDL_TIME      STATUS  EDITION_NAME
---------- ------------ ------------ ------------------ ------- ------------
    119435 PEOPLE_DL    PACKAGE      01-DEC-17 20:03:19 VALID   ORA$BASE
    119436 PEOPLE_DL    PACKAGE BODY 01-DEC-17 20:03:20 VALID   ORA$BASE
    119438 APP_MGR      PACKAGE BODY 01-DEC-17 20:03:24 VALID   ORA$BASE
    119437 APP_MGR      PACKAGE      01-DEC-17 20:03:24 VALID   ORA$BASE
    120794 PEOPLE_DL    PACKAGE BODY 15-DEC-17 11:29:53 VALID   V1

Yes, we have two package bodies named PEOPLE_DL in our schema – one is actual in ORA$BASE and the other in V1, and they are two different objects and each one has its own OBJECT_ID.

At this point our upgrade is done, but not exposed yet to the clients.
Let’s move to the client session, which is still using the ORA$BASE edition, and do the same query from USER_OBJECTS:

ORA$BASE> select object_id,
  2         object_name,
  3         object_type,
  4         last_ddl_time,
  5         status,
  6         edition_name
  7  from   user_objects
  8  where  object_type like 'PACK%'
  9  order  by last_ddl_time;

 OBJECT_ID OBJECT_NAME  OBJECT_TYPE  LAST_DDL_TIME      STATUS  EDITION_NAME
---------- ------------ ------------ ------------------ ------- ------------
    119435 PEOPLE_DL    PACKAGE      01-DEC-17 20:03:19 VALID   ORA$BASE
    119436 PEOPLE_DL    PACKAGE BODY 01-DEC-17 20:03:20 VALID   ORA$BASE
    119437 APP_MGR      PACKAGE      01-DEC-17 20:03:24 VALID   ORA$BASE
    119438 APP_MGR      PACKAGE BODY 01-DEC-17 20:03:24 VALID   ORA$BASE

Note how the client still sees the old instance of PEOPLE_DL and is not aware of the new one. And indeed, the following call shows the old implementation is still used:

ORA$BASE> exec app_mgr.do_something

person added using the BASE version

PL/SQL procedure successfully completed.

And now let’s simulate exposing of the post-upgrade version to the client, simply by setting its session edition to V1, and call the same procedure again:

ORA$BASE> @set_edition v1

Session altered.

V1> exec app_mgr.do_something

wow! this is not the base edition anymore

PL/SQL procedure successfully completed.

Et voilà – the client is now using the new implementation.

In the next post we’ll look at the second use-case – an online upgrade which includes a change of a package spec.
Our starting point will be the current state – here are the actual objects and dependencies at this point:

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