Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle

26 May 2011

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.
  • 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
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
  if inserting or updating then
    :new.comment_txt_v2 := :new.comment_txt;
  end if;
blog comments powered by Disqus