Edition-Based Redefinition in Oracle Database 11g Release 2

Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time.

There is a lot of detail in the documentation about this subject and I'm intentionally going to ignore most of it to keep this article as brief as possible. Instead I will give a little background information, then focus on two examples of its use to give you an idea of what it is capable of.

Editions
Enabling Editions for a User
Editioning Views
Crossedition Triggers
Dictionary Views
Example 1: No Data Transformation
Example 2: Two-Way Data Transformation

Editions

From Oracle database 11g release 2 onwards, each database has at least one edition, the default being ORA$BASE. The default edition can be displayed using the DATABASE_PROPERTIES view.

CONN / AS SYSDBA

SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_EDITION';

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

SQL>

The CREATE ANY EDITION and DROP ANY EDITION privileges are required to create and drop editions, so edition management is best done from privileged users.

Editions are created using the CREATE EDITION command with an optional AS CHILD OF clause.

CREATE EDITION edition-name;
CREATE EDITION edition-name AS CHILD OF parent-edition;

If the AS CHILD OF is omitted, the parent is assumed to be the leaf edition. In 11gR2 an edition can only have a single child, so this clause is not needed, but hints that editions with multiple children will be possible in future. Attempting to create an edition with multiple children results in the following error.

ORA-38807: Implementation restriction: an edition can have only one child

The DBA_EDITIONS view displays all database editions.

CREATE EDITION release_v1;
CREATE EDITION release_v2;

SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
RELEASE_V1                     ORA$BASE                       YES
RELEASE_V2                     RELEASE_V1                     YES

SQL>

The default database edition can be switched using the ALTER DATABASE command.

ALTER DATABASE DEFAULT EDITION = edition-name;

Only leaf editions can be dropped. Attempting to drop an edition in the middle of the chain results in the following error.

ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child

The following code shows that dropping the leaf edition works as expected.

DROP EDITION release_v2;

SELECT * FROM dba_editions;

EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
RELEASE_V1                     ORA$BASE                       YES

SQL>

If the editions have any associated editionable objects, then the CASCADE clause must be used to drop the editionable objects also.

Enabling Editions for a User

Enabling editions for a user is done using the ALTER USER command. This is not reversible. The result of this command can be seen by querying the EDITIONS_ENABLED column of the DBA_USERS view.

CONN / AS SYSDBA

CREATE USER edition_test IDENTIFIED BY edition_test DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER TO edition_test;

ALTER USER edition_test ENABLE EDITIONS;

SELECT editions_enabled
FROM   dba_users
WHERE  username = 'EDITION_TEST';

E
-
Y

SQL>

If the user contains existing non-editionable objects that depend on editionable objects, you must include the FORCE clause. This will leave the non-editionable objects in an invalid state.

Specific editions are granted to users with the USE ON EDITION clause.

GRANT USE ON EDITION release_v1 TO edition_test;

Grant succeeded.

SQL>

The edition is set at session level using the ALTER SESSION. The current edition for the session can be displayed using the SYS_CONTEXT function.

CONN edition_test/edition_test

ALTER SESSION SET EDITION = release_v1;

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

EDITION
--------------------------------------------------------------------------------
RELEASE_V1

SQL> 

-- Switch back to original edition.
ALTER SESSION SET EDITION = ora$base;

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

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

SQL>

When a new edition is used in a schema, all existing editionable object are inherited by the new edition from its parent. If any of those objects are changed using DML, then they are said to be actualized, along with their dependents, as a complete copy is made to the new edition.

Editioning Views

The presence of editions allows use to run multiple versions of the PL/SQL applications in a schema, but what happens if the code requires table changes, such as additional columns or renamed columns? This is where editioning views come into play. Editioning views are a wrapper over the base table that allow each edition to see the table in the form it needs. They can only be a straight query of the base table, but can display a subset of the columns and alias them to mimic a rename.

CREATE OR REPLACE EDITIONING VIEW view-name AS
SELECT col1,
       col3,
       col4 AS new_name
FROM   table-name;

By default editioning views are read-write, but they can be defined as read-only if a specific edition is kept for read-only use. Triggers can be applied directly to editioning views, rather than the base table, which allows triggers to be edition-specific. The *_EDITIONING_VIEWS and *_EDITIONING_VIEWS_AE views display all editioning views and actualized editioning views respectively.

The documentation contains a full explanation of editioning views, but you can see how they can be used in the examples below.

Crossedition Triggers

The presence of an editioning view displaying a different representation of a base table may present a bit of a problem when DML is applied to it. A forward crossediton trigger takes data from the columns in the old edition and transforms it to the that required by the new edition, while reverse crossedition triggers do the opposite. This means that both editions can be used simultaneously without causing logical corruption of the data in the base table.

There is a lot of detail about crossedition triggers in the documention, but you can see how they can be used in Example 2 below.

Dictionary Views

The following views contain information relating to editions or editioning components:

Example 1: No Data Transformation

In this example we add a new column to a base table, but want to run the existing application code against an editioning view with the new column omitted, and a new version of the application code against the amended table. This example assumes the EDITION_TEST user and RELEASE_V1 edition described earlier have already been created and the edition has been granted to the user.

First, we connect to the user, check the session edition and create the base table and procedure that represents our application.

CONN edition_test/edition_test

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

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

SQL>

CREATE TABLE employees (
  employee_id   NUMBER(5)    NOT NULL,
  name          VARCHAR2(40) NOT NULL,
  date_of_birth DATE         NOT NULL,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

CREATE SEQUENCE employees_seq;

CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, name, date_of_birth)
  VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth);
END create_employee;
/

Test the procedure to make sure it is working as expected.

BEGIN
  create_employee('Peter Parker', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10

SQL>

By checking the USER_OBJECTS view we can see that the editionable objects (the procedure) are assigned to the session edition.

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------------------
CREATE_EMPLOYEE      PROCEDURE           ORA$BASE
EMPLOYEES            TABLE
EMPLOYEES_PK         INDEX
EMPLOYEES_SEQ        SEQUENCE

SQL>

Now switch to the new edition and run the procedure again, just to prove it has been inherited by the new edition.

ALTER SESSION SET EDITION = release_v1;

BEGIN
  create_employee('Clark Kent', TO_DATE('02-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10

SQL>

This represents our original application. Now we need to make the change to the base table.

ALTER SESSION SET EDITION = ora$base;

ALTER TABLE employees ADD (
  postcode   VARCHAR2(20)
);

To make sure our application code can run unchanged we rename the base table and create an editioning view with the original table structure.

RENAME employees TO employees_tab;

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       name,
       date_of_birth
FROM   employees_tab;

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10

SQL>

Now we recompile the original procedure, making sure we use the REUSE SETTINGS clause, and test it.

ALTER PROCEDURE create_employee COMPILE REUSE SETTINGS;

BEGIN
  create_employee('Flash Gordon', TO_DATE('03-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B
----------- ---------------------------------------- ---------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10
          4 Flash Gordon                             03-JAN-10

SQL>

Next we switch to the new edition and create an editioning view to reflect the new table structure.

ALTER SESSION SET EDITION = release_v1;

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       name,
       date_of_birth,
       postcode
FROM   employees_tab;

We can now compile and test the new version of the procedure against the modified table (editioning view).

CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE,
                                             p_postcode      IN employees.postcode%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, name, date_of_birth, postcode)
  VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth, p_postcode);
END create_employee;
/

BEGIN
  create_employee('Mighty Mouse', TO_DATE('04-JAN-2010', 'DD-MON-YYYY'), 'AA1 2BB');
  COMMIT;
END;
/

SET LINESIZE 100
SELECT * FROM employees;

EMPLOYEE_ID NAME                                     DATE_OF_B POSTCODE
----------- ---------------------------------------- --------- --------------------
          2 Peter Parker                             01-JAN-10
          3 Clark Kent                               02-JAN-10
          4 Flash Gordon                             03-JAN-10
          5 Mighty Mouse                             04-JAN-10 AA1 2BB

SQL>

When we check the USER_OBJECTS view we see the new editioning view and procedure are assigned to the new edition.

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------------------
CREATE_EMPLOYEE      PROCEDURE           RELEASE_V1
EMPLOYEES            VIEW                RELEASE_V1
EMPLOYEES_PK         INDEX
EMPLOYEES_SEQ        SEQUENCE
EMPLOYEES_TAB        TABLE

SQL>

If we switch back to the original edition, the previous version of the editioning view and procedure are still present.

ALTER SESSION SET EDITION = ora$base;

COLUMN object_name FORMAT A20

SELECT object_name, object_type, edition_name
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------------------
CREATE_EMPLOYEE      PROCEDURE           ORA$BASE
EMPLOYEES            VIEW                ORA$BASE
EMPLOYEES_PK         INDEX
EMPLOYEES_SEQ        SEQUENCE
EMPLOYEES_TAB        TABLE

SQL>

So we have a single base table, being accessed by two different versions of the same application in a single schema. The version of the application used depends on the sessions edition setting.

Example 2: Two-Way Data Transformation

In the previous example we represented our employee name in a single column (NAME). In this example we will split that column into two columns (FIRST_NAME and LAST_NAME). We know how the three columns relate to each other so we can transform the data using crossedition triggers such that each edition populates the other editions view of the table correctly. First we alter the base table.

ALTER SESSION SET EDITION = release_v1;

ALTER TABLE employees_tab ADD (
  first_name VARCHAR2(20),
  last_name  VARCHAR2(20)
);

If we had down time available we could populate the new columns and make them NOT NULL like this.

UPDATE employees_tab
SET    first_name = SUBSTR(name, 1, INSTR(name, ' ')-1),
       last_name  = SUBSTR(name, INSTR(name, ' ')+1)
WHERE  first_name IS NULL;

ALTER TABLE employees_tab MODIFY (
  first_name VARCHAR2(20) NOT NULL,
  last_name  VARCHAR2(20) NOT NULL
);

Let's assume we can't have downtime on the original edition. Next we amend the editioning view and procedure in the new edition to reflect the table changes.

CREATE OR REPLACE EDITIONING VIEW employees AS
SELECT employee_id,
       first_name,
       last_name,
       date_of_birth,
       postcode
FROM   employees_tab;

CREATE OR REPLACE PROCEDURE create_employee (p_first_name    IN employees.first_name%TYPE,
                                             p_last_name     IN employees.last_name%TYPE,
                                             p_date_of_birth IN employees.date_of_birth%TYPE,
                                             p_postcode      IN employees.postcode%TYPE) AS
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, date_of_birth, postcode)
  VALUES (employees_seq.NEXTVAL, p_first_name, p_last_name, p_date_of_birth, p_postcode);
END create_employee;
/

Notice that neither the editioning view or procedure make any reference to the old NAME column. To make sure both the old and new columns stay in sync we need two cross edition triggers. The forward crossedition trigger takes data from the old edition and transforms it for use by the new edition. In this case that involves splitting the old NAME column into the new FIRST_NAME and LAST_NAME columns.

CREATE OR REPLACE TRIGGER employees_fwd_xed_trg
  BEFORE INSERT OR UPDATE ON employees_tab
  FOR EACH ROW
  FORWARD CROSSEDITION
  DISABLE
BEGIN
  :NEW.first_name := SUBSTR(:NEW.name, 1, INSTR(:NEW.name, ' ')-1);
  :NEW.last_name  := SUBSTR(:NEW.name, INSTR(:NEW.name, ' ')+1);
END employees_fwd_xed_trg;
/

The reverse crossedition trigger does the reverse by concatenating the new values together to represent the old column value.

CREATE OR REPLACE TRIGGER employees_rvrs_xed_trg
  BEFORE INSERT OR UPDATE ON employees_tab
  FOR EACH ROW
  REVERSE CROSSEDITION
  DISABLE
BEGIN
  :NEW.name := :NEW.first_name || ' ' || :NEW.last_name;
END employees_rvrs_xed_trg;
/

Once both triggers are in place we can enable them.

ALTER TRIGGER employees_fwd_xed_trg ENABLE;
ALTER TRIGGER employees_rvrs_xed_trg ENABLE;

Notice that both crossedition triggers were created in the child edition. If they are created in the parent edition the transformation will not work.

COLUMN object_name FORMAT A25

SELECT object_name, object_type, edition_name
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME               OBJECT_TYPE         EDITION_NAME
------------------------- ------------------- ------------------------------
CREATE_EMPLOYEE           PROCEDURE           RELEASE_V1
EMPLOYEES                 VIEW                RELEASE_V1
EMPLOYEES_FWD_XED_TRG     TRIGGER             RELEASE_V1
EMPLOYEES_PK              INDEX
EMPLOYEES_RVRS_XED_TRG    TRIGGER             RELEASE_V1
EMPLOYEES_SEQ             SEQUENCE
EMPLOYEES_TAB             TABLE

SQL>

At this point any new data applied to the tables will be populated properly, regardless of the edition that is used. Before we can proceed we need to make sure the old data is updated to reflect the change, then we can make the columns NOT NULL. We could use the method explained previously, or we can use the new cross edition trigger to do it for us. First we check that there are no outstanding DML operations on the base table.

DECLARE
  l_scn              NUMBER  := NULL;
  l_timeout CONSTANT INTEGER := NULL;
BEGIN
  IF NOT DBMS_UTILITY.wait_on_pending_dml(tables  => 'employees_tab',
                                          timeout => l_timeout,
                                          scn     => l_scn)
  THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: ' || l_scn);
  END IF;
END;
/

Then we use the DBMS_SQL package to update the base table, specifying the crossedition trigger to use to transform the data.

DECLARE
  l_cursor NUMBER := DBMS_SQL.open_cursor();
  l_return NUMBER;
BEGIN
  DBMS_SQL.PARSE(
    c                          => l_cursor,
    Language_Flag              => DBMS_SQL.NATIVE,
    Statement                  => 'UPDATE employees_tab SET name = name',
    apply_crossedition_trigger => 'employees_fwd_xed_trg'
  );
  l_return := DBMS_SQL.execute(l_cursor);
  DBMS_SQL.close_cursor(l_cursor);
  COMMIT;
END;
/

If the table were bigger we may want to parallelize the operation using the DBMS_PARALLEL_EXECUTE package, whose RUN_TASK procedure also supports the APPLY_CROSSEDITION_TRIGGER parameter.

Now the crossedition triggers are enabled and the data is up to date we can make the new columns NOT NULL.

ALTER TABLE employees_tab MODIFY (
  first_name VARCHAR2(20) NOT NULL,
  last_name  VARCHAR2(20) NOT NULL
);

Now to test the mechanism is working correctly. First we test the new edition.

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

EDITION
----------------------------------------------------------------------------------------------------
RELEASE_V1

SQL> 

BEGIN
  create_employee('Wonder', 'Woman', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A11 2BB');
  COMMIT;
END;
/

-- Check the editioning view.
SET LINESIZE 100
SELECT * FROM employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME            DATE_OF_B POSTCODE
----------- -------------------- -------------------- --------- --------------------
          2 Peter                Parker               01-JAN-10
          3 Clark                Kent                 02-JAN-10
          4 Flash                Gordon               03-JAN-10
          5 Mighty               Mouse                04-JAN-10 AA1 2BB
          6 Wonder               Woman                01-JAN-10 A11 2BB

SQL>

-- Check the base table.
COLUMN name FORMAT A15
COLUMN postcode FORMAT A10

SELECT * FROM employees_tab;

EMPLOYEE_ID NAME            DATE_OF_B POSTCODE   FIRST_NAME           LAST_NAME
----------- --------------- --------- ---------- -------------------- --------------------
          2 Peter Parker    01-JAN-10            Peter                Parker
          3 Clark Kent      02-JAN-10            Clark                Kent
          4 Flash Gordon    03-JAN-10            Flash                Gordon
          5 Mighty Mouse    04-JAN-10 AA1 2BB    Mighty               Mouse
          6 Wonder Woman    01-JAN-10 A11 2BB    Wonder               Woman

SQL>

Next we switch to the old edition and test that.

ALTER SESSION SET EDITION = ora$base;

ALTER PROCEDURE create_employee COMPILE REUSE SETTINGS;

BEGIN
  create_employee('Inspector Gadget', TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));
  COMMIT;
END;
/

-- Check editioning view.
COLUMN name FORMAT A25

SELECT * FROM employees;

EMPLOYEE_ID NAME                      DATE_OF_B
----------- ------------------------- ---------
          2 Peter Parker              01-JAN-10
          3 Clark Kent                02-JAN-10
          4 Flash Gordon              03-JAN-10
          5 Mighty Mouse              04-JAN-10
          6 Wonder Woman              01-JAN-10
          7 Inspector Gadget          01-JAN-10

SQL>

-- Check base table.
SELECT * FROM employees_tab;

EMPLOYEE_ID NAME                      DATE_OF_B POSTCODE   FIRST_NAME           LAST_NAME
----------- ------------------------- --------- ---------- -------------------- --------------------
          2 Peter Parker              01-JAN-10            Peter                Parker
          3 Clark Kent                02-JAN-10            Clark                Kent
          4 Flash Gordon              03-JAN-10            Flash                Gordon
          5 Mighty Mouse              04-JAN-10 AA1 2BB    Mighty               Mouse
          6 Wonder Woman              01-JAN-10 A11 2BB    Wonder               Woman
          7 Inspector Gadget          01-JAN-10            Inspector            Gadget

SQL>

So we have both applications running as required and the crossedition triggers are making sure the data is transformed correctly.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.