How Edition-Based Redefinition in Oracle Database 11gR2/12c/18c/19c works

Edition-based redefinition in Oracle Database 11gR2//12c/18c/19c 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 downtime. It also means we can  maintain multiple copies of the same object

What is Edition in Oracle?

An edition is effectively a version label that can be assigned to all editionable objects in a schema. When a new edition is used by a schema, all editionable objects are inherited by the new edition from the previous edition. These objects can subsequently then be altered or dropped as desired, but doing so will stop the inheritance of that object.

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

SQL> 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

CREATE EDITION R12_EDITION;

-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.

SQL> conn apps/apps
ALTER SESSION SET EDITION = R12_PATCH;
SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
EDITION
-----------
R12_PATCH
SQL>

Editionable Objects

The following objects are editionable:

• FUNCTION
• LIBRARY
• PACKAGE and PACKAGE BODY
• PROCEDURE
• TRIGGER
• TYPE and TYPE BODY
• SYNONYM
• VIEW

So basically the code objects are editioned. So application layer is fully editionable

The following objects are non-editionable:

• tables
• indexes
• materialized views

So basically the storage objects are non-editioned but the Application layer makes these objects effectively editioned

Dictionary Views for Edition

The following views contain information relating to editions or editioning components:
•*_EDITIONS
•*_EDITION_COMMENTS
•*_OBJECTS
•*_OBJECTS_AE
•*_ERRORS
•*_ERRORS_AE
•*_USERS
•*_VIEWS
•*_EDITIONING_VIEWS
•*_EDITIONING_VIEWS_AE
•*_EDITIONING_VIEW_COLS
•*_EDITIONING_VIEW_COLS_AE

How to make the existing application make use of Edition feature

(1)Rename base tables. Doing this will invalidate all PL/SQL code with the exception of triggers.
(2)Create editioning views over the base tables, matching the original table structures and names. PL/SQL objects will now recompile against the editioning view.
(3)If necessary, apply VPD policies against editioning views, rather than base tables.
(4) Revoke all privileges against tables and recreate them against the editioning views.

Let’s see this example (12c onwards)

Here is the structure of the Application before Edition

create sequence tech_test_seq start with 1 increment by 1 nocycle;

CREATE TABLE DEPT_MASTER (
dept_nr default tech_test_seq.nextval primary key,
dept_name varchar2(100) NOT NULL,
dept_status varchar2(10) NOT NULL,
created_at date
);

CREATE OR REPLACE PROCEDURE c_dept (p_dept_name IN DEPT_MASTER.dept_name%TYPE,
p_status IN DEPT_MASTER.dept_status%TYPE) AS
BEGIN
INSERT INTO DEPT_MASTER (dept_name, dept_status,created_at)
VALUES (p_dept_name, p_status,sysdate);
END create_dept;
/

BEGIN
c_dept('BANKING', Y);
COMMIT;
END;
/

SELECT * FROM DEPT_MASTER;
DEPT_NR     DEPT_NAME DEPT_STATUS CREATED_AT
----------- --------- --------- ---------
1            BANKING     Y       21-NOV-19 

Let’s do the restructuring using Edition based Redefinition

rename DEPT_MASTER to DEPT_MASTER_TAB;
CREATE OR REPLACE EDITIONING VIEW DEPT_MASTER AS
select dept_nr,dept_name,dept_status,created_at from DEPT_MASTER_TAB;

CREATE OR REPLACE PROCEDURE c_dept (p_dept_name IN DEPT_MASTER.dept_name%TYPE,
p_status IN DEPT_MASTER.dept_status%TYPE) AS
BEGIN
INSERT INTO DEPT_MASTER (dept_name, dept_status,created_at)
VALUES (p_dept_name, p_status,sysdate);
END create_dept;
/

BEGIN
c_dept('SALES', Y);
COMMIT;
END;
/

SELECT * FROM DEPT_MASTER;
DEPT_NR     DEPT_NAME DEPT_STATUS CREATED_AT
----------- --------- --------- ---------
1             BANKING    Y      21-NOV-19
2             SALES      Y      21-NOV-19

Now how to make changes without impacting Application

We can add the column to the base table, but our original application will be unaffected as it is accessing through editioning views

alter table DEPT_MASTER_TAB add ( location varchar2(10));

For the changes to propagate to the application, we will first create a new edition and then change the editioning views and procedure in it without impacting the original application as it is still connecting to the original edition

CREATE EDITION R12_EDITION;
ALTER SESSION SET EDITION = R12_EDITION;

CREATE OR REPLACE EDITIONING VIEW DEPT_MASTER AS
select dept_nr,dept_name,dept_status,created_at,location from DEPT_MASTER_TAB;

CREATE OR REPLACE PROCEDURE c_dept (p_dept_name IN DEPT_MASTER.dept_name%TYPE,
p_status IN DEPT_MASTER.dept_status%TYPE,p_location IN DEPT_MASTER.location%TYPE) AS
BEGIN
INSERT INTO DEPT_MASTER (dept_name, dept_status,created_at,location)
VALUES (p_dept_name, p_status,sysdate,p_location);
END create_dept;
/

BEGIN
c_dept('TRAVEL', Y, 'BOSTEN');
COMMIT;
END;
/

Now at this point in time, we have one version of Edition view and procedure in the original edition and one in R12_EDITION. At the appropriate time, you can switch the application to start using the new edition.

R12.2 also makes use of the same feature to enable online patching.

(1)Schemas in the database are converted to be editions enabled
– Turns on object versioning in the database
– Once a schema is editions enabled it cannot be reverted
(2) Introduces a new view of the EBS data model
– A logical rather than the physical representation of the data model is presented
– Changes the way many objects are defined and patched
(3) All code must access the data model via the APPS synonym
(4) APPS synonym points to the Editioning View (Logical Model)
(5) Any code accessing the physical model risks accessing obsolete columns

Related articles
Oracle documentation on Edition Based Redefinition
forward cross edition triggers R12.2: Forward cross edition triggers are database triggers which work across edition and is used to sync data as part of the online patching process.

Conclusion

Oracle Edition-Based Redefinition is a powerful feature that can be utilized to have near-zero downtime of the application. Hope you like the article on Edition-Based Redefinition in Oracle Database 11gR2. Please do provide the feedback.  If you like this page, please do share it.