Edition Based Redefinition – Part 1

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.

– Frank Herbert, Dune (First Law of Mentat)



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.


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.


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:

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.


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.

To check which edition you are using you can issue this SQL statement:

This will show you which edition you are currently connected to and where your changes will take place.


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:

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:

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:

The default edition can be switched using the ALTER DATABASE command:

To drop an edition you use the DROP EDITION command:

You can only drop leaf editions. If you try to drop an edition that has a child this will result in the following error:

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.

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:

The edition can be set at session level using the ALTER SESSION command:

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.


To demonstrate Edition Based Redefinition we write the following code:

If we run this code the following will be displayed on the screen:

Then we create a child edition:

We change our session to the newly created child edition (Note: we don’t reconnect, we just alter the session scope)

If we run the code from the child edition, the following will be displayed on the screen:

Now we change the code for this procedure in the child edition:

And run in again to see the following result:

Now we change back to the parent edition and run the code again:

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.