DevOps in the database

Edition-Based Redefinition (EBR) can seem a little intimidating if you’ve a lot of old objects in a number of schemas, but given a few prerequisites, it’s not so challenging. The advantages are potentially enormous. 

In a 24×7 world, organisations can no longer afford even overnight temporary outages due to PL/SQL or data definition changes. Properly organised and tested, EBR enables user acceptance testing in the production database and controlled roll-out of enhanced functionality with no disruption for online users. 

Do you really want to hope automatic releases at midnight don’t leave you with a mess to clean up in the morning? How about if there was a way to deploy code and object changes with no disruption in core hours with Development, DBA and Operations support teams fully available in core Oracle Support hours?

Editioning for PL/SQL objects can be enabled using the following:

  • Create an edition according to a naming scheme
  • Enable editions in appropriate schemas
  • Set the session edition
  • Recompile the schema objects in editioned schemas
  • Set the database default edition
  • Let users log in to the new edition

If Development teams in your organisation supply scripts for deployment, they could be given the next edition name by a utility, or request one from the DBA/configuration control team. Dev scripts can be deployed in the new edition with no effect on live processes. All they need to do is set their session edition before making changes.

Once the database default edition is set, new sessions automatically only see the new edition.

Editioning for tables is initially a little more daunting, especially if you’re not in a “green field” environment.

All table objects in editioned schemas become editioning views. Changes can be made to the underlying tables without impacting current code…


It’s scary at first. You have the capability of making a big-time mess by ignoring the fact non-editioned objects cannot depend on editioned objects. There’s a reason not to edition-enable any schemas with objects which reference non-editioned objects. 

There’s also a problem with slightly obscure stuff like versioned ADTs and registered XML schema types.

If you have a green-field application, you should edition from the beginning. For most of the world then, you probably need to start with a select group of schemas, and version PL/SQL objects only, not tables at first.

EBR also has to be looked after, kept clean. You should disable access to old editions and drop them if possible as you cannot drop an edition that’s in the middle of a chain. (But there should be no problem with having thousands of editions.)

There’s also a problem with table-functions which use ADTs, possibly fixed in latest

However, if organised properly the headline benefits are:

  • Application upgrades during core time for the Devs, DBAs, other internal support and Oracle Support UK;
  • Final User Acceptance Testing on live data
  • No user sees the new edition until specifically enabled or the database default edition is changed and the user starts a new session. 

I’ve developed PL/SQL utility functions to enable editioning in a defined set of schemas and help Dev teams specify the right edition in their deployment scripts. If you need any help, please get in touch.

Bryn Lewellyn and Chris Saxon are probably the world’s leading experts on EBR and have some presentations worth a look here:

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close