News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Release 2 Edition-based Redefinition

  • Posted by Gavin Soorma
  • On September 21, 2009
  • 1 Comments

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.

 

1 Comments

john
  • Jan 20 2011
Hi Gavin Oracle promising that the application can be change online without downtime is false. Changing the edition is only available after a new connect. Is true the downtime can be reduced but still i have downtime and i need to stop many application servers to do the change... unfortunately....

Leave Reply

Your email address will not be published. Required fields are marked *