11g Release 2 Edition-based Redefinition

11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any outage as the application was accessing the procedure or package which needed to be modified.

An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.

Let us see an example of this where we create a procedure, make some changes to the procedure in a new edition and then make those changes visible to other database users.

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

Grant succeeded.

SQL> conn sh/sh
Connected.

SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> set serverout on

SQL> BEGIN hello(); END;
/
  2  Hello, edition 1.

PL/SQL procedure successfully completed.


SQL> CREATE EDITION e2;

Edition created.


SQL> ALTER SESSION SET EDITION = e2;

Session altered.


SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> BEGIN hello(); END;
  2  /
Hello, edition 2.

PL/SQL procedure successfully completed.

Note, now if we change the edition to the default edition ‘ORA$BASE’, we will now see the original procedure and not the updated one

SQL> ALTER SESSION SET EDITION = ora$base;

Session altered.

SQL>  BEGIN hello(); END;
  2  /
Hello, edition 1.

Let us now see how this can be rolled out to database user HR

SQL> grant execute on hello to hr;

Grant succeeded.


SQL> GRANT USE ON EDITION e2 to public;

Grant succeeded.

We now make the default edition of the database, E2 so that all changes in the new edition E2 will be visible to all database users

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> ALTER DATABASE DEFAULT EDITION=e2;

Database altered.


SQL> conn hr/hr
Connected.

SQL> set serverout on

SQL>  exec sh.hello;
Hello, edition 2.