OOW 2009: The killer feature of Oracle Database 11gR2 – Edition Based Redefinition (or database object versioning)

5

 

Today I presented on what is possibly the hottest story on the Oracle Database 11gR2 release: Edition Based Redefinition (EBR). EBR allows us to add a whole new dimension to the database – the Edition (that complements the existing dimensions of schema and object type). Every database object (well, almost every database object – not tables!) can have different implementations/incarnations/versions in various editions. The object versions are all in the same schema – they only differ in the Edition they are created in.

The Edition Based Redefinition has us create Editions in the database – cross database object spaces where new versions of existing database objects can be created – and others can just be inherited from previous editions. We can construct an Edition in isolation – no one is impacted by us creating new objects and versions of objects; objects can be invalid in that edition and no one will know anything about it. Only once the edition is complete, valid and tested can we make it available to new user sessions to work against. Using a simple alter session set edition statement, sessions can specify which edition they want to run against. That is: after the user has been granted access to that particular edition.

There are some mechanics that you should know about of course.

Especially the way tables are dealt with. Basically, the table will no longer exist as far as applications and PL/SQL code is concerned. Everything you used to do with and against tables will now be with or against Editionable Views. A thin wrapper around the table – that does not impact performance. This EV can only project columns – expose a subset of the columns from the table using column aliases – not use functions, constants, where clause or any other query facilities. The EV is the construct that can be editioned. And since all applications only see the view and no longer the table, it will appear as if the "table" is editioned – has different incarnations in different editions.

There is also a new type of trigger – the cross edition trigger – that is used to synchronize DML from a previous edition with the current state of the table (forward cross edition) and to do vice versa: synchronize DML against a new edition with the expectations in older editions. The presentation shown below demonstrates how this works. Note: the forward cross edition trigger can also be used to do the conversion of the existing data to the table definition as introduced in the new edition.

The presentation is shown here:

 

Resources

Review of my presentation ‘from the audience’: Editions Of You – Edition Based Redefinition

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. Hi Praveen,

     

    I am not sure exactly what you are trying to achieve. Oracle will simply not support EBR in EBS 11i. That is their decision. There is no technical limitation for doing it, they are simply not investing that way.

     

    Lucas

  2. Hi

    Very much impressed with your comments on EBR.  Good articale with Demo. 
    Had question on the use of EBR,  as its Database feature, why can’t this be implemented with Release 11i of E-Business suite and why to wait for R12 release to support it?  Please correct me if i am worng.  Please advise.

  3. Hi,

    Great presentation on EBR. I would greatly appreciate if you could suggest how to redesign the system with AQs to ready it for EBR.

    Regards,

    VRS

  4. Hi Rob,
    Good to hear from you again! And thanks for your comment. You are right with the analogy with Designer: the Edition really is very similar to the Configuration in Designer Repository/SCM. Given all the time I spent on that functionality in the past, I find it fairly easy to come to understand the 11gR2 EBR mechanism. Hope to meet you sometime in the near future.

    best regards,

    Lucas

  5. Lucas — thanks for once again trying out and making sense of what at first glance seemed like a questionable feature. The idea seemed good, but not likely to work (hmm, maybe based on Designer Element Version nightmares?). The key of course is the cross-edition triggers to impute valid / default values into the out-of-edition rows. Your clear explanation of how and when to use them is great! Thanks.