![Page 1: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/1.jpg)
Oracle Edition Based Redefinition
Presentation for
http://surachartopun.com
![Page 2: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/2.jpg)
Introduction
• Surachart Opun
• Blog: surachartopun.com
• Oracle ACE
• OCP 10G/11G and OCE (RAC)
• Member: OUGTH, IOUG, UKOUG, RAC SIG
• Twitter: @surachart
http://surachartopun.com
![Page 3: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/3.jpg)
Edition Based Redefinition (EBR)?
• New Feature on 11gR2
• Upgrade an application while it is in use
• Edition object types
– SYNONYM
– VIEW
– All PL/SQL object types: (FUNCTION,LIBRARY,PACKAGE and PACKAGE BODY,PROCEDURE,TRIGGER,TYPE and TYPE BODY)
http://surachartopun.com
![Page 4: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/4.jpg)
Three New Object Types• Edition
– Replacing Edition object types (PL/SQL, synonyms, and views)
• Editioning View
– Table is not an edition type. If you change the structure of one or more tables.
• Crossedtion trigger
– Other users must be able to change data in the tables while you are changing their structure
– If the pre- and post-upgrade applications will be in ordinary use at the same time
http://surachartopun.com
![Page 5: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/5.jpg)
Dictionary Views
• Edition
– dba_editions
– dba_edition_comments
• Editioning View
– dba_editioning_views
– dba_editioning_views_ae (edition_name)
– dba_editioning_view_cols
• Crossedition Trigger
– dba_triggers (crossedition)
http://surachartopun.com
![Page 6: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/6.jpg)
What is an Edition?• A nonschema object type, uniquely identified,
therefore, by just its name.
• Be able to check in DBA_OBJECTS.
select * from dba_objects where object_type= ’EDITION’;
• Every database from 11.2 onwards - Least one edition.
• The default edition name is ORA$BASE
http://surachartopun.com
![Page 7: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/7.jpg)
• A new edition must be created as the child of an existing one.
create edition version2 as child of ora$base;
• An edition may have no more than one child.
ORA$BASE <- VERSION2 <- VERSION3 <- VERSION4
create edition version2 as child of ora$base;
create edition version3 as child of version2;
create edition version4 as child of version3;
http://surachartopun.com
![Page 8: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/8.jpg)
SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USA
---------------------- ------------------------------ ---
ORA$BASE YES
VERSION4 VERSION3 YES
VERSION2 ORA$BASE YES
VERSION3 VERSION2 YES
http://surachartopun.com
![Page 9: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/9.jpg)
• A new database property, DEFAULT_EDITION.
alter database default edition = Some_Edition
SQL> select PROPERTY_NAME, PROPERTY_VALUE
from database_properties
where PROPERTY_NAME='DEFAULT_EDITION';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_EDITION ORA$BASE
http://surachartopun.com
![Page 10: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/10.jpg)
• A new alter session command allows the edition that a session is using to be changed.
alter session set edition = Some_Edition
SQL> alter session set edition=version3;
Session altered.
SQL> SELECT SYS_CONTEXT
('userenv','current_edition_name') ce FROM DUAL;
CE
----------
VERSION3http://surachartopun.com
![Page 11: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/11.jpg)
Edition Privileges• System Privileges
– CREATE ANY EDITION
– ALTER ANY EDITION
– DROP ANY EDITION
• Object Privileges - USE (not granted by default)
GRANT USE ON <edition_name> TO <user_name>;
• Rolessystem privileges are granted to the DBA role (only).
• Enable EditioningALTER USER <user_name> ENABLE EDITIONS;
http://surachartopun.com
![Page 12: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/12.jpg)
Edition Enabled Data Dictionary Views
• AUD$ (obj$edition)
• DBA_EDITIONS (edition_name, parent_edition_name)
• DBA_OBJECTS (edition_name)
• DBA_OBJECTS_AE (edition_name)
• DBA_SOURCE_AE (edition_name)
• DBA_USERS (editions_enabled)
• AE = All Editions
http://surachartopun.com
![Page 13: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/13.jpg)
Edition Enabled Data Dictionary Views
• FGA_LOG$ (obj$edition)
• UTL_RECOMP_ALL_OBJECTS (edition_name)
• V$LOGMNR_CONTENTS (edition_name)
• V$SESSION (session_edition_id)
• AE = All Editions
http://surachartopun.com
![Page 14: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/14.jpg)
SQL> SELECT * FROM DBA_EDITIONS;
EDITION_NAME PARENT_EDITION_NAME USA
------------------------------ ------------------------------ ---
ORA$BASE YES
SQL> CREATE EDITION VERSION2 AS CHILD OF ORA$BASE;
Edition created.
SQL> SELECT * FROM DBA_EDITIONS;EDITION_NAME PARENT_EDITION_NAME USA
------------------------------ ------------------------------ ---
ORA$BASE YES
VERSION2 ORA$BASE YES
SQL> ALTER USER DEMO ENABLE EDITIONS;
SQL> GRANT USE ON EDITION VERSION2 TO DEMO;http://surachartopun.com
![Page 15: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/15.jpg)
SQL> connect demo/demo
Connected.
SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;
CE
--------------------------------------------------------------------------------
ORA$BASE
SQL> create or replace procedure my_procedure
as
Begin
dbms_output.put_line ( 'I am version 1.0' );
end;
/
http://surachartopun.com
![Page 16: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/16.jpg)
SQL> ALTER SESSION SET EDITION=VERSION2;
Session altered.
SQL> create or replace procedure my_procedure
as
Begin
dbms_output.put_line ( 'I am version 2.0' );
end;
/
SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;
CE
--------------------------------------------------------------------------------
ORA$BASE
http://surachartopun.com
![Page 17: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/17.jpg)
SQL> exec my_procedure;
I am version 1.0
SQL> ALTER SESSION SET EDITION=VERSION2;
Session altered.
SQL> SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;
CE
--------------------------------------------------------------------------------
VERSION2
SQL> exec my_procedure;
I am version 2.0
http://surachartopun.com
![Page 18: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/18.jpg)
What is an Editioning View?
• Editioning view selects a subset of the columns from a single base table.
• Use the SQL statement CREATE VIEW with the OR REPLACE clause and the keyword EDITIONING.
CREATE OR REPLACE EDITIONING VIEW …
• Partition-Extended Editioning View Names
http://surachartopun.com
![Page 19: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/19.jpg)
Editioning View Related Data Dictionary Views
• DBA_EDITIONING_VIEW_COLS
• DBA_EDITIONING_VIEW_COLS_AE
• DBA_EDITIONING_VIEWS
• DBA_EDITIONING_VIEWS_AE
• DBA_ERRORS_AE (editioning_name)
• DBA_OBJECTS_AE (editioning_name)
• DBA_VIEWS (editioning_view)
• AE = All Editions
http://surachartopun.com
![Page 20: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/20.jpg)
SQL> CREATE EDITIONING VIEW ed01_tab AS SELECT name, tel_num FROM ed01_tab_tmp;
SQL> select * from ED01_TAB partition (par_1);
NAME TEL_NUM
------------------------------ ----------
TEST1 1234567
TEST5 1234567
SQL> CREATE TRIGGER ed01_tab_tri
BEFORE INSERT ON ed01_tab FOR EACH ROW
BEGIN
null;
END;
/
Trigger created.
http://surachartopun.com
![Page 21: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/21.jpg)
What is a Crossedition Trigger?
• A new, and special type of trigger specific to editioning
• Can only be created on a table (not on an editioningview)
• Propagates transactions between editions
• Two types
– FORWARD
– REVERSE
http://surachartopun.com
![Page 22: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/22.jpg)
• Crossedition triggers are always created in the child
• Crossedition triggers are temporary—drop them after you have made the restructured tables available to all users.
http://surachartopun.com
![Page 23: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/23.jpg)
Crossedition Trigger Types
• Forward Crossedition Triggers
– Only fired by code running in the parent edition
– Transforms from the old representation to the new
• Reverse Crossedition Triggers
– Only fired by code running in the child edition
– Transforms from the new representation to the old
http://surachartopun.com
![Page 24: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/24.jpg)
Crossedition Trigger Related Data Dictionary Views
• DBA_TRIGGERS
• DBA_TRIGGER_ORDERING
• DBA_ERRORS_AE (editioning_name)
• DBA_OBJECTS_AE (editioning_name)
• AE = All Editions
http://surachartopun.com
![Page 25: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/25.jpg)
CREATE TABLE PERSONS (ID NUMBER(10) NOT NULL, FIRST_NAME VARCHAR2(100) NOT NULL, LAST_NAME VARCHAR2(100) NOT NULL,EMAIL VARCHAR2(100) NOT NULL)
/
RENAME persons TO persons_tab;
CREATE EDITIONING VIEW persons AS
SELECT id, first_name AS firstname, last_name AS lastname, email FROM persons_tab;
create sequence persons_seq;
http://surachartopun.com
![Page 26: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/26.jpg)
CREATE OR REPLACE TRIGGER persons_bi_trg
BEFORE INSERT ON persons FOR EACH ROW
BEGIN
:new.id := persons_seq.nextval;
END;
/
insert into persons values (null,'Somchai','A.','[email protected]');
commit;
ALTER TABLE persons_tab ADD (email_recipient VARCHAR2(100),email_domainVARCHAR2(100));
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL
---------- --------------- --------------- --------------------
1 Somchai A. [email protected]
http://surachartopun.com
![Page 27: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/27.jpg)
alter session set edition=VERSION2;
CREATE TRIGGER persons_fc_trg
BEFORE INSERT OR UPDATE ON persons_tab FOR EACH ROW
FORWARD CROSSEDITION
DISABLE
BEGIN
:new.email_recipient :=
regexp_substr(:new.email,'(.*)@',1,1,NULL,1);
:new.email_domain :=
regexp_substr(:new.email,'@(.*)',1,1,NULL,1);
END;
/
http://surachartopun.com
![Page 28: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/28.jpg)
column CE for a10
SELECT SYS_CONTEXT ('userenv','current_edition_name') ce FROM DUAL;
CREATE OR REPLACE EDITIONING VIEW persons AS
SELECT id, first_name AS firstname, last_name AS lastname,email_recipient,email_domain FROM persons_tab;
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL_RECIPIENT EMAIL_DOMAIN
---------- --------------- --------------- -------------------- --------------------
1 Somchai A.
http://surachartopun.com
![Page 29: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/29.jpg)
ALTER TRIGGER persons_fc_trg ENABLE;
DECLARE
c INTEGER;
r INTEGER;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(
c => c,
statement => 'UPDATE persons SET email_domain = email_domain',language_flag => dbms_sql.native,
apply_crossedition_trigger => 'persons_fc_trg');
r := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
COMMIT;
END;
/
http://surachartopun.com
![Page 30: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/30.jpg)
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL_RECIPIENT EMAIL_DOMAIN
---------- --------------- --------------- -------------------- --------------------
1 Somchai A. somchai test.com
---TEST FORWARD ---
alter session set edition=ORA$BASE;
update persons set email='[email protected]' where id=1;
commit;
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL
---------- --------------- --------------- -------------------------
1 Somchai A. [email protected]
http://surachartopun.com
![Page 31: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/31.jpg)
alter session set edition=version2;
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL_RECIPIENT EMAIL_DOMAIN
---------- --------------- --------------- -------------------- --------------------
1 Somchai A. surachart gmail.com
http://surachartopun.com
![Page 32: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/32.jpg)
---TEST REVERSE---
alter session set edition=version2;
CREATE TRIGGER persons_rc_trg
BEFORE INSERT OR UPDATE ON persons_tab FOR EACH ROW
REVERSE CROSSEDITION
DISABLE
BEGIN
:new.email := :new.email_recipient || '@' || :new.email_domain;
END;
/
alter trigger persons_rc_trg enable;
insert into persons values (null,'Surachart','O.','surachart',‘test.com');
commit;
http://surachartopun.com
![Page 33: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/33.jpg)
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL_RECIPIENT EMAIL_DOMAIN
---------- --------------- --------------- -------------------- --------------------
1 Somchai A. surachart gmail.com
2 Surachart O. surachart test.com
alter session set edition=ORA$BASE;
SQL> select * from persons;
ID FIRSTNAME LASTNAME EMAIL
---------- --------------- --------------- ------------------------------
1 Somchai A. [email protected]
2 Surachart O. [email protected]
http://surachartopun.com
![Page 34: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/34.jpg)
Learn More…
• Oracle Documents (E11882_01)
http://surachartopun.com
![Page 35: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/35.jpg)
Learn More..
• Tom Kyte on Edition Based Redefinition
http://surachartopun.com
![Page 36: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/36.jpg)
Learn More…
• Bryn Llewellyn's White Paper
http://surachartopun.com
![Page 37: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/37.jpg)
Learn More…
• Morgan Library www.morganslibrary.org
http://surachartopun.com
![Page 38: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/38.jpg)
Learn More…
• IOUG – Select Journal
http://surachartopun.com
![Page 39: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/39.jpg)
Q&A
http://surachartopun.com
![Page 40: Basic - Oracle Edition Based Redefinition Presentation](https://reader034.vdocuments.us/reader034/viewer/2022042521/55958cdd1a28abd8798b467d/html5/thumbnails/40.jpg)
THANK YOU
http://surachartopun.com