Edition Based Redefinition - Cheat Sheet

Rules of Thumb

• Keep it as simple as possible
• Limit the time both applications are running as much as possible
• All tables should have an Editioning View created against them that the application must access instead of the table
• The application should have no privileges on the base tables
• Despite the protection offered by Editioning Views, 'select *' and inserts without listing columns should be avoided.
• All normal triggers should be moved from the base table to the Editioning Views.
• Simple schema changes can only involve adding columns to existing tables or adding completely new tables.
• You can never drop or alter existing columns which are referenced by an Editioning View that is in use by any live part of the application.
• Indexes required for the upgrade should be created as invisible and altered to visible when the upgrade is complete.
• When PLSQL units need to be changed, they should be added to a new Edition.

Complex schema changes are possible using Cross Edition Triggers:

• Try to avoid chains of triggers, which can get confusing fast
• Ensure that only two Editions are ever in use on the database at any time.
• Take care with normal and cross edition DML
• Test, test and test some more away from production

Other things to investigate

• DBMS_PARALLEL to avoid locking the entire table during a transform.
• The IGNOREROWONDUPKEYINDEX hint
• The APPLYINGCROSSEDITIONTRIGGER function.
• DBMSSESSION.SETEDITION_DEFERRED
• Oracle Advanced Developers Guide - Chapter 19.

Useful Commands

Default Database Edition

SQL11G> SELECT property_value
        FROM   database_properties
        WHERE  property_name = 'DEFAULT_EDITION';

Add an Edition

SQL11G> create edition upgrade_v2;

Grant an Edition

SQL11G> grant use on edition upgrade_v2 to public;

Revoke an Edition

SQL11G> revoke use on edition upgrade_v2 from public;

Switch default Edition (grants use to public automatically)

SQL11G> alter database default edition = upgrade_v2;

Current Session Edition

SQL11G> select sys_context('Userenv', 'Current_Edition_Name')
from dual;

Switch Edition

SQL11G> alter session set edition = new_edition_name;

What sessions are using an Edition

TODO (doesn't seem to be possible?)

Editioning Views

SQL11G> create or replace editioning view
as
select col from table;

Cross Edition Trigger Syntax

SQL11G> create or replace trigger user_comments_fwd_xed_trg
before insert or update or delete on user_comments
for each row
forward crossedition
disable
begin
  if inserting or updating then
    :new.comment_txt_v2 := :new.comment_txt;
  end if;
end;
/