1 / 18

Edition-Based Redefinition

Edition-Based Redefinition. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Pre-Upgrade. Post-Upgrade. Old Edition. New Edition. Edition-Based Redefinition Overview. Introduced in Oracle 11.2 Online Application Upgrade Goals are:

lulu
Download Presentation

Edition-Based Redefinition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related