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.
- Oracle Advanced Developers Guide - Chapter 19.
Default Database Edition
SQL11G> SELECT property_value
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')
SQL11G> alter session set edition = new_edition_name;
What sessions are using an Edition
TODO (doesn't seem to be possible?)
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
if inserting or updating then
:new.comment_txt_v2 := :new.comment_txt;