Edition Based Redefinition

Previously you could not compile any PL/SQL that was currently being executed. If you did, the result would be an ORA-04068 “Existing state of packages has been discarded”. This restriction is lifted in Oracle 11g rel 2. You can now perform an online upgrade of an application. In other words you can patch code while users are executing the old version. You can also do some schema changes while users access the old version. The goal of this new capability is to ensure that downtime to users is minimized.

Objects in general fall into two categories. They are either editionable or noneditionable. Editionable objects includes functions, procedures, package, and so on. They can be easily upgraded online. Noneditionable objects are things like database tables. Normally you cannot upgrade them online. However with a new object called an editioning view, you can even simulate changes to database tables while users are on the system running your apps.

An editioning view has specific limitations. You can only select from this view. Furthermore you can only reference one database table (and thus no joins are permitted). This view acts like a synonym in that it projects attributes from the underlying table. This view can have triggers. The trick is that this view can effectively hide columns from the user. This hiding operation is similar to dropping a column a table. The new kind of column drop is a virtual one. The column in the underlying table remains, but access to it has been shut off.

Here is the operational practice to use such editioning views. You first rename the underlying table. Then you create an editioning view that has the same name as the old table. You drop the triggers on the original table. Recreate the triggers on the new view. Revoke any privs on the base table. This is what we call a hot rollover. There are other similar features in Oracle 11g rel 2. Perhaps I will cover them in a future post.