Edition based Redefinition – 11gR2
Edition-based redefinition is a new feature of Oracle 11g Release 2 and it’s a ‘killer’. When you want to upgrade the application by making changes in the database and code like Pl/sql, the edition-based feature ensures you with ZERO DOWNTIME.
Background: Take a case that you want to make some changes in database like modifying the column size, adding new columns, deleting columns,etc,. then you would see that many objects going invalid and the application will be affected. If you enabled edition-based feature, then the old codes, database structures are maintained without affecting the application & end users. Then, at the same time, we can do modifications to new version.
What are Editions?
Oracle database now has editions where we can have different users using different editions. Every schema has at least one edition (ORA$BASE) but each edition will have a child edition.
SQL> select property_value from database_properties where property_name='DEFAULT_EDITION';
PROPERTY_VALUE
------------------------------
ORA$BASE
When a new edition is used by a schema, all editionable objects are inherited by new edition from the previous/parent edition. Eventhough, if you have objects inherited from previous one, when you drop or modify the objects, it will stop doing so to the inheritance of that object.
Editionable Objects
Not all objects can be different in different editions. For eg., the tables need to be same for all editions. But we can add columns to them without affecting the current code. Currently, this is handled by fine grained dependency tracking. But when you add a column to a table, depending code will no longer become invalid which used to happen in older database versions. Following objects are editionable and can have different implementations in different editions of a schema.
Ø Synonym
Ø Views
Ø Functions
Ø Package
Ø Procedure
Ø Types
Ø Library and
Ø Trigger
Following are the exceptions
Ø Tables for instance
Ø Public synonyms are non-editionable.
Pre-requisites for Edition
To create an edition, we need to have CREATE ANY EDITION and DROP ANY edition system privilege. If we want to create edition as a child of another edition, you must have the USE object privilege on the parent edition.
Grant create any edition, drop any edition to <username>;
Alter user <username> enable editions;
To check what is the edition we are using , we can issue the following statement.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
ORA$BASE
Users are also granted access to specific editions using the below statement.
Grant use on edition <edition_name> to <username>;
Editions
An edition is a complete set of all schema objects like views,programs,etc. When we create an edition, all objects are inherited from the parent edition (by default ORA$BASE). When we make a change to the objects of child edition, the inheritance is stopped. When we create new objects in new edition, it will not be visible to the parent edition.
Enabling Editions in the database and session level
Below statements are used for enabling the editions at database level or session level.
Alter session set edition=<edition_name>;
Alter database default edition=<edition_name>;
DBA_editions show the default,childand parent editions.
Mutiple Editions
If you want to create multiple editions, the new edition will inherit from previous child edition.
SQL> create edition edition2;
Edition created.
SQL> select * from dba_Editions;
EDITION_NAME PARENT_EDITION_NAME USA
------------------------------ ------------------------------ ---
ORA$BASE YES
EDITION1 ORA$BASE YES
EDITION2 EDITION1 YES
If you try to create multiple editions for single parent, you will end up with an error.
SQL> create edition edition3 as child of ORA$BASE;
create edition edition3 as child of ORA$BASE
*
ERROR at line 1:
ORA-38807: Implementation restriction: an edition can have only one child
To check which are the users are using EDITIONS and enabled, we can check from dba_users.
SQL> select username,editions_enabled from dba_users where editions_enabled='Y';
USERNAME E
------------------------------ -
TEST1 Y
Example1 How table creation does not have any impact on Edition based
1. Login as test1 user whom we have given ‘create any edition’ and ‘drop any edition’ privileges.
SQL> show user;
USER is "SYS"
SQL> grant create any edition,drop any edition to test1;
Grant succeeded.
SQL> alter user test1 enable editions;
User altered.
Conn test1/test1
SQL> show user;
USER is "TEST1"
2. Check for the current edition for the user in current session.
select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
ORA$BASE
3. Now we will create a new edition under TEST1 user.
SQL> create edition edition1;
Edition created.
4. Alter the session to use new edition ‘EDITION1’
SQL> alter session set edition=edition1;
Session altered.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
EDITION1
5. Now, we will create NON-editionable objects and see whether they are available in other session. As name suggests, it should be available in other sessions.
SQL> create table testedition (id number(10),city varchar2(30));
Table created.
SQL> insert into testedition values(1,'Mumbai');
1 row created.
SQL> insert into testedition values(2,'Delhi');
1 row created.
SQL> commit;
6. Now check for the objects under the session using the new edition.
SQL> select object_name,object_type from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------- -------------------
RKI_INDEX INDEX
INSERT_RKI PROCEDURE
LOAD_DATA PROCEDURE
NOVALIDTABLE TABLE
TESTNOBKP TABLE
BKPTEST TABLE
RKI_DETAILS TABLE
TEST_ACTIVE4 TABLE
TEST_ACTIVE5 TABLE
RKI TABLE
TESTEDITION TABLE
BLKTEST TABLE
SQL> select * from TESTEDITION;
ID CITY
---------- ------------------------------
1 Mumbai
2 Delhi
Check for the objects under the session which is using a default session (ORA$BASE)
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
ORA$BASE
SQL> select object_name,object_type from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
RKI_INDEX INDEX
INSERT_RKI PROCEDURE
LOAD_DATA PROCEDURE
TEST_ACTIVE4 TABLE
NOVALIDTABLE TABLE
RKI_DETAILS TABLE
BLKTEST TABLE
BKPTEST TABLE
TEST_ACTIVE5 TABLE
RKI TABLE
TESTEDITION TABLE
TESTNOBKP TABLE
SQL> select * from TESTEDITION;
ID CITY
---------- ------------------------------
1 Mumbai
2 Delhi
In the above results, we see that the table TESTEDITION is available in other session which is using the default edition.
We also see that data of a new table are accessible from two different versions of a schema with a single base table. The version of application used depends upon the sessions edition setting.
Example 2Next, we will see what happens when we create an EDITIONABLE object.
1. Login as Test1 user.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
EDITION1
SQL> create synonym testedit for testedition;
Synonym created.
SQL> select object_name,object_type from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------- -------------------
RKI_INDEX INDEX
INSERT_RKI PROCEDURE
LOAD_DATA PROCEDURE
TESTEDIT SYNONYM
NOVALIDTABLE TABLE
TESTNOBKP TABLE
BKPTEST TABLE
RKI_DETAILS TABLE
TEST_ACTIVE4 TABLE
TEST_ACTIVE5 TABLE
RKI TABLE
TESTEDITION TABLE
BLKTEST TABLE
2. Check in Session using ORA$BASE
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDIT
-----------------------------------
ORA$BASE
SQL> select object_name,object_type from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
RKI_INDEX INDEX
INSERT_RKI PROCEDURE
LOAD_DATA PROCEDURE
TEST_ACTIVE4 TABLE
NOVALIDTABLE TABLE
RKI_DETAILS TABLE
BLKTEST TABLE
BKPTEST TABLE
TEST_ACTIVE5 TABLE
RKI TABLE
TESTEDITION TABLE
TESTNOBKP TABLE
Its noted that the editionable object (synonym) is not available in other edition. .
3. Now, let’s alter this session also to use the edition EDITION1 and see whether we are able to see the synonym TESTEDIT.
[oracle@vm1 ~]$ sqlplus test1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 19 11:49:43 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter session set edition=edition1;
Session altered.
SQL> set lines 150
SQL> set pages 30000
SQL> col object_name format a35
SQL> select object_name,object_type from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
RKI_INDEX INDEX
INSERT_RKI PROCEDURE
LOAD_DATA PROCEDURE
TESTEDIT SYNONYM
NOVALIDTABLE TABLE
TESTNOBKP TABLE
BKPTEST TABLE
RKI_DETAILS TABLE
TEST_ACTIVE4 TABLE
TEST_ACTIVE5 TABLE
RKI TABLE
TESTEDITION TABLE
BLKTEST TABLE
Now, it’s available even-though we open new session and set EDITION to new/child edition, it is available.
Example 3: Let us see more by using simple Pl/SQL procedures.
1. Login to a sql plus with default edition and create a pl/sql procedure.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
ORA$BASE
create or replace procedure test_edition
is
edit_name varchar2(30);
BEGIN
select sys_context('USERENV','CURRENT_EDITION_NAME') into edit_name from dual;
dbms_output.put_line('The edition name is '||edit_name);
END;
/
SQL> exec test_edition;
The edition name is ORA$BASE
2. Now log in to another session and alter the session to use EDITION1 (child of ORA$BASE) and execute the procedure.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_N
----------------------------------------
ORA$BASE
SQL> alter session set edition=edition1;
Session altered.
SQL> set serverout on
SQL> exec test_edition;
The edition name is EDITION1
The reason why we are able to execute the procedure of ORA$BASE is that the object is inherited. But now, if we make a change to this procedure in EDITION1 , it will not reflect in the parent edition.
3. In the session using EDITION1, we change the code for the pl/sql block as below and executed.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_N
----------------------------------------
EDITION1
create or replace procedure test_edition
is
edit_name varchar2(30);
BEGIN
select sys_context('USERENV','CURRENT_EDITION_NAME') into edit_name from dual;
dbms_output.put_line('The edition name is '||edit_name);
dbms_output.put_line ('Welcome to my new edition' ||edit_name);
END;
/
SQL> exec test_edition
The edition name is EDITION1
Welcome to my new editionEDITION1
4. But now, if we execute the procedure from the default edition session, we see the output is not changed.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_N
----------------------------------------
ORA$BASE
SQL> exec test_edition
The edition name is ORA$BASE
How to check to which Editions are used by the editionable objects that are used in our previous example (SYNONYM,PROCEDURE) . This can be viewed from user_objects_ae view.
SQL> select object_name,status,object_type,edition_name from user_objects_ae;
OBJECT_NAME STATUS OBJECT_TYPE EDITION_NAME
----------------------------------- ------- ------------------- ------------------------------
RKI_DETAILS VALID TABLE
RKI_INDEX VALID INDEX
BKPTEST VALID TABLE
LOAD_DATA VALID PROCEDURE ORA$BASE
INSERT_RKI VALID PROCEDURE ORA$BASE
BLKTEST VALID TABLE
TESTNOBKP VALID TABLE
NOVALIDTABLE VALID TABLE
TEST_ACTIVE4 VALID TABLE
TEST_ACTIVE5 VALID TABLE
RKI VALID TABLE
TESTEDITION VALID TABLE
TESTEDIT VALID SYNONYM EDITION1
TEST_EDITION VALID PROCEDURE EDITION1
TEST_EDITION VALID PROCEDURE ORA$BASE
No comments:
Post a Comment