Oracle Edition-Based Redefinition (EBR)

EBR was introduced in Oracle 11g Release 2 and available for use in all editions of Oracle Database without the need to license it.

EBR allow us to version control code and eligible objects within the database for a user session, so different sessions could potentially see and use different code bases depending on their settings.

To use EBR the user who owns the objects must be created or altered using the ‘ENABLE EDITIONS’ syntax to support EBR, this is not reversible after doing. An example is shown below:

CREATE USER PSAMMY_EBR1 identified by abcd1234 enable editions;

OR

CREATE USER PSAMMY_EBR2 identified by abcd1234;
ALTER USER PSAMMY_EBR2 enable editions;

We can also check which users have EBR enabled using:

select username from dba_users where editions_enabled='Y';

What objects can we EDITION, this can be queried as below and will vary depending on compatible setting.

select * from V$EDITIONABLE_TYPES

A user can check their current edition using below:

SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_EDITION_NAME’) AS edition FROM dual;

OR to check the EDITIONS in play we can use v$session and dba_objects

select username, sid, serial#, object_name edition_name
from v$session v, dba_objects do
where v.session_edition_id=do.object_id
and username is not null;

A database will have a default Edition which can be viewed using the below SQL, the default on a will be ORA$BASE.

SELECT property_value
 FROM   database_properties
 WHERE  property_name = 'DEFAULT_EDITION';

We can create new editions using ‘CREATE EDITION’

CREATE EDITION APP_RELEASE_V1_2;
CREATE EDITION APP_RELEASE_V1_2_3;

Once we create an edition we can then grant use of that edition to a user who has the Edition capability enabled using:

GRANT USE ON EDITION APP_RELEASE_V1_2 to PSAMMY_EBR1, PSAMMY_EBR2;
GRANT USE ON EDITION APP_RELEASE_V1_2_3 to PSAMMY_EBR1, PSAMMY_EBR2;

We can view which EDITIONS exist using:

SELECT * FROM DBA_EDITIONS;

To set the current edition at session level we use:

ALTER SESSION SET EDITION = APP_RELEASE_V1_2;
ALTER SESSION SET EDITION = APP_RELEASE_V1_2_3;

Or we can change the default for the database using:

ALTER DATABASE DEFAULT EDITION = APP_RELEASE_V1_2_3;

We can now perform a small test

  • Create a user that supports EBR
  • Create 2 Editions for version control
  • Create a version controlled procedure
  • Test running of the procedures
  • Change DB default Edition

CREATE USER EBR_TEST1 identified by abcd1234 enable editions;
GRANT CREATE SESSION, CREATE PROCEDURE to EBR_TEST1;

CREATE EDITION APP_RELEASE_V1_2;
CREATE EDITION APP_RELEASE_V1_2_3;
GRANT USE ON EDITION APP_RELEASE_V1_2 to EBR_TEST1;
GRANT USE ON EDITION APP_RELEASE_V1_2_3 to EBR_TEST1;

Log in as user EBR_TEST1

CREATE OR REPLACE PROCEDURE PROC_EBR_1
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('APP_RELEASE_BASE');
END;
/

ALTER SESSION SET EDITION = APP_RELEASE_V1_2;
CREATE OR REPLACE PROCEDURE PROC_EBR_1
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('APP_RELEASE_V1_2');
END;
/

ALTER SESSION SET EDITION = APP_RELEASE_V1_2_3;
CREATE OR REPLACE PROCEDURE PROC_EBR_1
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('APP_RELEASE_V1_2_3');
END;
/

From a new session logged in as EBR_TEST1

set serverout on
exec PROC_EBR_1

ALTER SESSION SET EDITION = APP_RELEASE_V1_2;
exec PROC_EBR_1

ALTER SESSION SET EDITION = APP_RELEASE_V1_2_3;
exec PROC_EBR_1

We should see that the invoking of the same procedure under different EBR runs different code and generates different output.

Now as a sys user modify db default to latest EBR, log back in as EBR_TEST1 and test default.

ALTER DATABASE DEFAULT EDITION = APP_RELEASE_V1_2_3;

Log into a new session as EBR_TEST1

exec PROC_EBR_1