A process cannot be understood by stopping it. Understanding must move with the flow of the process, must join it and flow with it.
– Frank Herbert, Dune (First Law of Mentat)
One of the ‘killer’ features of the Oracle Database version 11g Release 2 is probably Edition Based Redefinition. This functionality allows for near zero downtime when performing an upgrade for your application.
Introduction
Systems nowadays need to be available almost 24/7. For instance, the electricity utility company needs to be able to monitor the power flow 24 hours a day, 7 days a week. But these systems need upgrading just like any other system, unless you want to your electricity to be controlled by yesterdays ideas and software. Another system that needs to be available 24/7 is a support system for a global company. If the people in Asia are
asleep or not using the system, then the people in the USA are most likely to use the system. You don’t want your system to be down at any time, but you also want to improve your system, implement changes, apply improvements etc. This puts you in kind of a catch-22 situation.
Background
Since Oracle 11g release 2 the database now comes with Edition Based Redefinition. This technique provides you with the possibility to apply upgrades without invalidating the system. Before 11gR2, when you apply a change to the database, it might render a lot of objects to be invalid and you would need recompilation of your code. There are parts of the system that can be changed without this problem occuring, like the bodies of packages, but when you were to change the size of a column for instance, all the depending code needs to be checked and therefore needs to be recompiled. This could mean a problem for ‘running’ sessions, causing them to break. Using Edition Based Redefinition you can build the new or improved system and have users start using it without the ‘old’ system going down.
Editions
The database now implements Editions where you can have different users use different editions. Every schema has at least one edition (ORA$BASE), but every edition can have a child edition. At this moment just a single child, but if we look at the syntax to create such an edition:
|
1 2 3 |
CREATE EDITION edition [ AS CHILD OF parent_edition ] ; |
Chances are that Oracle will support multiple children in the future.
Editionable objects
Not all objects can be different in the different editions. For instance the tables need to be the same for all editions. But you can add columns to them without invaliding the current code. This is mainly handled by the fine grained dependency tracking that is implemented nowadays. When you add a column to a table, depending code will no longer render invalid where it used to invalidate the code in older databases. The following objects are editionable, hence they can have different implementations in different editions of the schema:
- Synonym
- View
- Function
- Procedure
- Package (specification and body)
- Type (specification and body)
- Library
- Trigger
Every other object type that is not in this list is not editionable, like the table for instance. There is one exception to the rules: Although SYNONYM is an editionable type, a public synonym is a noneditioned object.
Prerequisites
To create an edition, you must have the CREATE ANY EDITION system privilege, granted either directly or through a role. To create an edition as a child of another edition, you must have the USE object privilege on the parent edition.
|
1 2 3 4 5 |
GRANT CREATE ANY EDITION, DROP ANY EDITION to EBR; Grant succeeded. ALTER USER ebr ENABLE EDITIONS; User altered. |
To check which edition you are using you can issue this SQL statement:
|
1 |
SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL; |
This will show you which edition you are currently connected to and where your changes will take place.
Editions
An edition is a complete set of editionable objects in a schema, including editioning views, programs etc. When you create a new edition, all editionable objects are inherited from the parent. If you make changes to the objects, inheritance will be stopped. You can even drop objects in a child edition without affecting the object in the parent edition. When you create objects in the child edition, they will not be visible in the parent edition. From Oracle database version 11g release 2, each database has at least one edition, being ORA$BASE, the default edition.
To create or drop an edition, the user needs to have the CREATE ANY EDITION and the DROP ANY EDITION privileges granted. To create an edition use the CREATE EDITION command:
|
1 2 |
CREATE EDITION child-edition; CREATE EDITION child-edition AS CHILD OF parent-edition; |
The AS CHILD OF clause is optional. If it is omitted then the parent is assumed to be the leaf edition. If you try to create a child edition on a parent that already has a child edition it causes the following error to be raised:
|
1 |
ORA-38807: Implementation restriction: an edition can have only one child |
Editions are created instance wide and not on a user or schema level. If you connect to the database you always connect to the default edition. If nothing is done, this will be ORA$BASE. You can switch the edition for a single session using the ALTER SESSION command:
|
1 |
ALTER SESSION SET EDITION = edition-name; |
The default edition can be switched using the ALTER DATABASE command:
|
1 |
ALTER DATABASE DEFAULT EDITION = edition-name; |
To drop an edition you use the DROP EDITION command:
|
1 |
DROP EDITION edition-name; |
You can only drop leaf editions. If you try to drop an edition that has a child this will result in the following error:
|
1 |
ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child |
If the editions to be dropped have any associated editionable objects, then the CASCADE clause must be included in the drop statement to drop the editionable objects also.
Enabling Editions
Enabling editions for a user is done using the ALTER USER command. This cannot be undone. You cannot revoke this from the user.
|
1 |
ALTER USER username ENABLE EDITIONS; |
If the user contains existing non-editionable objects that are dependent upon editionable objects you must include the FORCE clause. This will leave all the non-editionable objects in an invalid state. Users are granted access to specific editions using the USE ON EDITION clause of the GRANT command:
|
1 |
GRANT USE ON EDITION edition-name TO username; |
The edition can be set at session level using the ALTER SESSION command:
|
1 |
ALTER SESSION SET EDITION = edition-name; |
When a schema is using a new edition, all existing editionable objects are inherited by the new edition from its parent. If any of those objects are changed in the parent edition then a complete copy is made to the new edition, along with their dependents.
Programs
To demonstrate Edition Based Redefinition we write the following code:
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE PROCEDURE say_hello IS l_edition VARCHAR2(30); BEGIN SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') INTO l_edition FROM DUAL; DBMS_OUTPUT.PUT_LINE('Hello from '||l_edition); END; |
If we run this code the following will be displayed on the screen:
|
1 2 |
SQL> EXEC say_hello Hello from ORA$BASE |
Then we create a child edition:
|
1 |
CREATE EDITION child_edition AS CHILD OF ORA$BASE; |
We change our session to the newly created child edition (Note: we don’t reconnect, we just alter the session scope)
|
1 |
ALTER SESSION SET EDITION = child_edition; |
If we run the code from the child edition, the following will be displayed on the screen:
|
1 2 |
SQL> EXEC say_hello Hello from CHILD_EDITION |
Now we change the code for this procedure in the child edition:
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE PROCEDURE say_hello IS l_edition VARCHAR2(30); BEGIN SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') INTO l_edition FROM DUAL; DBMS_OUTPUT.PUT_LINE('Hello from edition '||l_edition||'.'); END; |
And run in again to see the following result:
|
1 2 |
SQL> EXEC say_hello Hello from edition CHILD_EDITION. |
Now we change back to the parent edition and run the code again:
|
1 2 |
SQL> EXEC say_hello Hello from ORA$BASE |
As you can see, the changes have not been applied in the parent edition. In the next article we will see how to handle data in different editions.
Load comments