Edition-Based Redefinition

1. Edition-BasedRedefinition Julian Dyke Independent Consultant Web Version juliandyke.com

2. Pre-Upgrade Post-Upgrade Old Edition New Edition Edition-Based RedefinitionOverview • Introduced in Oracle 11.2 • Online Application Upgrade • Goals are: • Installation of upgrade into production database is invisible to current users of pre-upgrade application • Transactions performed by users of pre-upgrade application visible in post-upgrade application • Transactions performed by users of post-upgrade application visible in pre-upgrade application Cross-EditionTriggers Schema & Code Changes

3. Edition-Based RedefinitionLife Cycle • Pre-Upgrade • Period before upgrade commences. Only one edition used by every session • Upgrade • Create a new edition • Upgrade objects and code • Concurrent use of pre and post-upgrade edition • Pre-upgrade session continue to use pre-upgrade sessions • Changes made in new edition are not visible • Cross-edition triggers propagate data changes made in old edition to new edition and vice versa • Post Upgrade Phase • When all pre-upgrade sessions have ended pre-upgrade edition can be retired • Post upgrade edition becomes default database edition

4. Edition-Based RedefinitionEditions • Editions are non-schema object types • Every database has at least one edition • A new edition is the child of an existing edition • Every database session uses an edition • An edition is identified by a unique name • An object has an implicit edition name • Only schema-qualified name is required • Edition part is supplied automatically by database • Initial edition is called ORA$BASE • The edition used by a session is called the current edition • Can be changed for example: ALTER SESSION SET EDITION = post_upgrade; • The default edition can be changed using: ALTER DATABASE DEFAULT EDITION = post_upgrade;

5. Edition-Based RedefinitionEditionable Objects • The following object types are editionable • Synonyms • Views • PL/SQL objects • Packages • Procedures • Functions • Tables and indexes are not editionable

6. Edition-Based RedefinitionExample • Grant use of edition to users CONNECT / AS SYSDBA CREATE EDITION edition2 AS CHILD OF ORA$BASE; • Allow USER1 to work with editions ALTER USER user1 ENABLE EDITIONS; • Allow USER1 to work with EDITION2 GRANT USE ON EDITION edition2 TO user1; • Note that an edition can only have one child

7. Edition-Based RedefinitionExample CONNECT user1/user1 CREATE OR REPLACE PROCEDURE hello ISBEGIN dbms_output.put_line ('Version 1');END;/ ALTER SESSION SET EDITION = edition2; CREATE OR REPLACE PROCEDURE hello ISBEGIN dbms_output.put_line ('Version 2');END;/ ALTER SESSION SET EDITION = ORA$BASE; EXECUTE hello; 'Version 1' ALTER SESSION SET EDITION = edition2; EXECUTE hello; 'Version 2'

8. Edition-Based RedefinitionEditioning Views • Tables and indexes are non-editionable • Due to amount of data • Multiple copies across editions would consume too much space • Editioning views • New type of view in Oracle 11.2 and above • Logically presents different views of a table across different editions • Use keyword EDITIONING during creation • Can be associated with same types of triggers as base tables CREATE OR REPLACE EDITIONING VIEW view1AS SELECT * FROM t1; • To list editioning views SELECT owner,view_name FROM dba_viewsWHERE editioning_view = 'Y';

9. Edition-Based RedefinitionCross-Edition Triggers • Actual objects in the post-upgrade edition • Can be • forward • reverse • Forward cross-edition triggers • Fired by application DML issued by sessions using pre-upgrade edition • Transforms changes made to pre-upgrade columns into changes to post-upgrade columns • Reverse cross-edition triggers • Optional but recommended • Fired by application DML issued by sessions using post-upgrade edition • Transforms changes made to post-upgrade columns into changes to pre-upgrade columns

10. Edition-Based RedefinitionExample • Create edition and grant privileges to USER1 CONNECT / AS SYSDBA ALTER USER user1 ENABLE EDITIONS; CREATE EDITION edition2; GRANT USE ON EDITION edition2 TO user1; • Rename table CONNECT user1/user1 ALTER TABLE driver RENAME TO driver_; • Create editioning view on table CONNECT user1/user1 CREATE EDITIONING VIEW driver ASSELECT driver_key, driver_name, driver_dob, country_keyFROM driver_;

11. Edition-Based RedefinitionExample • In new edition modify underlying table CONNECT user1/user1 ALTER SESSION SET EDITION = edition2; ALTER TABLE driver_ ADD driver_first_name VARCHAR2(30); ALTER TABLE driver_ ADD driver_last_name VARCHAR2(30); • In new edition create new version of editioning view: CONNECT user1/user1 CREATE OR REPLACE EDITIONING VIEW driver ASSELECT driver_key, driver_name, driver_dob, country_key, driver_first_name,driver_last_nameFROM driver_;

12. Edition-Based RedefinitionExample • In new edition create FORWARD trigger ALTER SESSION SET EDITION = edition2; CREATE OR REPLACE TRIGGER driver_forwardBEFORE INSERT OR UPDATE ON driver_FOR EACH ROWFORWARD CROSSEDITIONDISABLEBEGIN :new.driver_first_name := SUBSTR (:new.driver_name,1,INSTR (:new.driver_name,' ')); :new.driver_last_name := SUBSTR (:new.driver_name,INSTR (:new.driver_name,' ') + 1);END;/ • In new edition enable FORWARD trigger ALTER SESSION SET EDITION = edition2; ALTER TRIGGER driver_forward ENABLE;

13. Edition-Based RedefinitionExample • In new edition create REVERSE trigger ALTER SESSION SET EDITION = edition2; CREATE OR REPLACE TRIGGER driver_reverseBEFORE INSERT OR UPDATE ON driver_FOR EACH ROWREVERSE CROSSEDITIONDISABLEBEGIN :new.driver_name := :new.driver_first_name||' '||:new.driver_last_name;END;/ • In new edition enable REVERSE trigger ALTER SESSION SET EDITION = edition2; ALTER TRIGGER driver_reverse ENABLE;

14. Edition-Based RedefinitionExample • In the new edition use the forward trigger to populate the values in the new columns: ALTER SESSION SET EDITION = edition2; DECLARE c NUMBER := dbms_sql.open_cursor (); x NUMBER;BEGIN dbms_sql.parse ( c => c, language_flag => DBMS_SQL.NATIVE, statement => 'UPDATE driver SET driver_key = driver_key', apply_crossedition_trigger => 'DRIVER_FORWARD' ); x := DBMS_SQL.EXECUTE (c); DBMS_SQL.CLOSE_CURSOR (c);END;/

15. Edition-Based RedefinitionExample • In the original edition insert a new row ALTER SESSION SET EDITION = edition1;INSERT INTO driver (driver_key,driver_name,driver_dob,country_code)VALUES ('JBUT','Jenson Button',NULL,'GBR'); • In the new edition check that the cross edition trigger has fired ALTER SESSION SET EDITION = edition2;SELECT driver_first_name, driver_last_name FROM driver WHERE driver_key = 'JBUT'; DRIVER_FIRST_NAMEDRIVER_LAST_NAMEJenson Button

16. Edition-Based RedefinitionExample • In the new edition insert a new row: ALTER SESSION SET EDITION = edition2;INSERT INTO driver (driver_key,driver_first_name,driver_last_name,driver_dob,country_code)VALUES ('JBUT','Jenson','Button',NULL,'GBR'); • In the old edition check that the cross edition trigger has fired ALTER SESSION SET EDITION = ORA$BASE;SELECT driver_name FROM driver WHERE driver_key = 'JBUT'; DRIVER_NAMEJenson Button

17. Edition-Based RedefinitionCross-Edition Triggers • Cross edition triggers may include FOLLOWS and PRECEDES clauses • FOLLOWS clause • May be specified on a FORWARD cross-edition trigger or a non cross-edition trigger • Indicates trigger should be executed after a specified trigger • If trigger B specifies trigger A in its FOLLOWS clause then trigger B executes immediately after trigger A • PRECEDES clause • May only be specified on a REVERSE cross-edition trigger • Indicates trigger should be executed before a specified trigger • If trigger B specifies trigger A in its PRECEDES clause then trigger A executes immediately after trigger B

18. Thank you for your interest info@juliandyke.com