Last updated on September 28th, 2016 at 05:09 am
Edition-based redefinition in Oracle Database 11gR2 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.
-The CREATE ANY EDITION and DROP ANY EDITION privileges are required to create and drop editions
-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.
ALTER SESSION SET EDITION = R12_PATCH;
SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_EDITION_NAME’) AS edition FROM dual;
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:
3) 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.
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
– 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 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.