How to Create Different Editions or Versions for Oracle Objects? Its Advantages

advertisements

_____________________________________________________________________________________________________________________

In oracle you can create different versions for the objects which are not requiring the storage. For example Table requires storage and View doesn’t require the storage. These are the types of the objects can be editionable.

• Synonym
• View
• Function
• Procedure
• Package
• Type
• Library
• Trigger

Editioning needs to be enabled in the schema to perform this task. Main advantage of this feature is you can keep different or multiple versions of objects which will be very useful during the application upgrades. For each and every database the default edition will be ORA$BASE which will be considered as the parent or first edition for all the objects.

Example to create Edition for a object?

Schema requires create any edition privilege and editioning needs to be enabled.

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to scott;

Grant succeeded.

SQL> alter user scott enable editions;

User altered.

Creating new edition with the user SCOTT.

SQL>conn scott/tiger

CREATE EDITION NEW_EDITION;

Edition created.

By default the user object will be under ORA$BASE edition and you can check using following query.

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')

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

ORA$BASE

I am going to create an edition view in ORA$BASE edition. It is having 3 columns from emp table.

SQL> CREATE EDITIONING VIEW ed_emp_view_ORABASE as select  EMPNO,ENAME,JOB from emp;

View created.

SQL> desc ed_emp_view_ORABASE

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

Here I am going to create a new edition for the same view in NEW_EDITION.  You have to use create or replace option to create the new edition view. Otherwise it will throw the error “object already exists”.

Backup Process of Object Editions

In below example I am going to create a new edition for the same view with same name. It is having 4 different columns than the above.

SQL> ALTER SESSION SET EDITION =NEW_EDITION;

Session altered.

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')

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

NEW_EDITION

CREATE or replace EDITIONING VIEW ed_emp_view_ORABASE as select  EMPNO,ENAME,SAL,COMM from emp;

SQL> desc ed_emp_view_ORABASE

 Name                                      Null?    Type

 ----------------------------------------- -------- --------------- EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

Using the following query you can check your object is belongs to which edition.

select OBJECT_NAME, EDITION_NAME from USER_objects where object_name='ED_EMP_VIEW_ORABASE'

SQL> /

OBJECT_NAME                    EDITION_NAME

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

ED_EMP_VIEW_ORABASE            NEW_EDITION

You can create child edition for a parent edition using “as child of” clause

Syntax: create edition <edition name> as child of <parent edition>;

You cannot have more than one child for a main edition. IF you try to create more than one child you will get following error.

CREATE EDITION NEW_EDITION2 as child of NEW_EDITION;

CREATE EDITION NEW_EDITION2 as child of NEW_EDITION

*

ERROR at line 1:

ORA-38807: Implementation restriction: an edition can have only one child

SQL> ALTER SESSION SET EDITION = NEW_EDITION;

Session altered.

select OBJECT_NAME, EDITION_NAME from USER_objects where object_name='ED_EMP_VIEW_ORABASE'

SQL> /

OBJECT_NAME                    EDITION_NAME

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

ED_EMP_VIEW_ORABASE            NEW_EDITION

SQL>  ALTER SESSION SET EDITION = ORA$BASE;

Session altered.

SQL>

SQL> select OBJECT_NAME, EDITION_NAME from USER_objects where object_name='ED_EMP_VIEW_ORABASE';

OBJECT_NAME                    EDITION_NAME

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

ED_EMP_VIEW_ORABASE            ORA$BASE

_____________________________________________________________________________________________________________________

Website Stats