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 down time. 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 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>
The following objects are editionable:
- PACKAGE and PACKAGE BODY
- TYPE and TYPE BODY
So basically the code objects are editioned. So application layer is fully editionable
The following objects are non editionable:
- materialized views
So basically the storage objects are non editioned but Application layer makes these objects effectively editioned
Dictionary Views for Edition
The following views contain information relating to editions or editioning components:
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 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.
Lets see this example (12c onwards)
Here is the structure of 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
Lets do the restructing 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 propagated to the application, we will first create a new edition and then change the editioning views and procedure in that with out impacting original application as it is still connecting to 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 of time , we have one version of Edition view and procedure in original edition and one in R12_EDITION. At appropiate time, you can switch the application to start using the new edition.
R12.2 also makes use of 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 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
Oracle documentation on Edition Based Redifination
forward cross edition triggers R12.2 : Forward cross edition triggers are database triggers which works across edition.and is used to sync data as part of online patching process.
Oracle Edition-Based Redefinition is a powerful feature which 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.