How Edition-Based Redefinition in Oracle Database 11gR2 works

Last updated on September 28th, 2016 at 05:09 am

Edition-Based Redefinition in Oracle Database 11gR2Edition-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.


SELECT property_value

FROM database_properties
WHERE property_name = ‘DEFAULT_EDITION’;




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

conn apps/apps







Editionable Objects

The following objects are editionable:

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

The following objects are non editionable:

1) tables
2) indexes
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

Related articles

Oracle documentation on Edition Based Redifination

forward cross edition triggers R12.2


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.

Leave a Reply