Wednesday 22 April 2015

Edition Based Redefinition - 11gR2 Onwards

Edition based redefinition is a new feature from 11gR2 that allows multiple versions of the same (Edition-able object) PL/SQL objects, views and synonyms in a single schema. Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time. 

From 11gR2 onwards every database will have atleast one edition, a default edition named ORA$BASE, It is stored as a database property. The default edition can be displayed using the DATABASE_PROPERTIES

SQL> select property_value from database_properties where property_name='DEFAULT_EDITION' ;
PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE


  • To check which edition is enabled in the current session.


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

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

  • To create new edition
CREATE EDITION edition-name;
CREATE EDITION edition-name AS CHILD OF parent-edition;


e.g. -->

CREATE EDITION release_v1;
CREATE EDITION release_v2 AS CHILD OF release_v1;
CREATE EDITION release_v3;
CREATE EDITION release_v4;

SELECT * FROM dba_editions;

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

***************


In 11gR2 an edition can only have a single child. 

Objects can be categorized in two types:-

Editonable objects - Synonyms, Views, All PL/SQL object types - Function, Library, Package, Package Body, Procedure, Trigger, Type and Type Body.

Non-Editionable objects - All other schema object types are non-Editionable. Table is an example of an non-Editionable type. Public synonym is also nonEditionable.



Grant or revoke privileges to create, alter, and drop editions --
             GRANT CREATE ANY EDITION, DROP ANY EDITION to  <user> ;
Enable editions for a schema -- ALTER USER user_name ENABLE EDITIONS FORCE;
Set the database default edition - ALTER DATABASE DEFAULT EDITION = edition_name
Set the edition attribute of a database service -
            srvctl add service -d dbcrm -s crmbatch -t e2  --  database managed by oracle restart
            use the DBMS_SERVICE.CREATE_SERVICE procedure, and specify the edition parameter
Setting the Edition Attribute of an Existing Database Service




-------------------------------------------------------