Table of contents
Preamble
Edition-Based Redefinition (EBR) has been introduced in Oracle database 11gr2. And the feature is free:
EBR is available for use in all editions of Oracle Database without the need to license it.
The purpose is to handle database application code change without impacting users than can continue to work transparently. Users can be migrated at different time or by bunch of application server for example.
For the noneditioned objects (like tables) you must use different strategies. To go further in my test exercise I have club EBR with a very old feature exposed by DBMS_REDEFINITION package. This feature that I have personally never used is there since Oracle database 9i (!!)
My test database will be a pluggable database inside a 21c (21.6) Enterprise Edition instance. My test server is running Redhat 7.9 (Maipo), has 12 cores and 64GB of memory.
Edition-Based Redefinition (EBR) and DBMS_REDEFINITION test case
One table with a primary key:
SQL> CREATE TABLE test01 ( id INTEGER, first_name VARCHAR2(20) NOT NULL, last_name VARCHAR2(20) NOT NULL, CONSTRAINT test01_pk PRIMARY KEY(id) ); TABLE created. |
One trigger to format first name and last name:
SQL> CREATE OR REPLACE TRIGGER test01_first_last_name before INSERT OR UPDATE ON test01 FOR each ROW BEGIN :NEW.first_name:=INITCAP(:NEW.first_name); :NEW.last_name:=INITCAP(:NEW.last_name); END; / TRIGGER created. |
One procedure to display a welcome message with user id as parameter:
SQL> CREATE OR REPLACE PROCEDURE greet(pid NUMBER) IS first_name VARCHAR2(20); last_name VARCHAR2(20); BEGIN SELECT first_name, last_name INTO first_name, last_name FROM test01 WHERE id=pid; dbms_output.put_line('Hello ' || first_name || ', ' || last_name); END; / PROCEDURE created. |
I insert a new row and confirm everything is working fine:
SQL> INSERT INTO test01(id,first_name,last_name) VALUES(1,'yannick','JAQUIER'); 1 ROW created. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test01; ID FIRST_NAME LAST_NAME ---------- -------------------- -------------------- 1 Yannick Jaquier SQL> SET serveroutput ON SQL> EXEC greet(1); Hello Yannick, Jaquier PL/SQL PROCEDURE successfully completed. |
Allow your test user to manipulate editions:
SQL> ALTER USER yjaquier enable editions; USER altered. |
Edition-Based Redefinition (EBR) testing
In my small test case the high level objective is to modify my base table (test01) to add a full_name column that will be the concatenation of first_name and last_name and use this full_name column is the new greet procedure. The test01_first_last_name must aso be modified to table into account this newly added column.
Application preparation for EBR
To list the objects that are editionable (as explained in official documentation table objects are not):
SQL> SET lines 200 SQL> col object_name FOR a30 SQL> col edition_name FOR a10 SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects; OBJECT_NAME OBJECT_TYPE E EDITION_NA ------------------------------ ----------------------- - ---------- TEST01 TABLE TEST01_PK INDEX TEST01_FIRST_LAST_NAME TRIGGER Y GREET PROCEDURE Y 6 ROWS selected. |
The currently existing edition:
SQL> col parent_edition_name FOR a20 SQL> SELECT * FROM all_editions; EDITION_NA PARENT_EDITION_NAME USA ---------- -------------------- --- ORA$BASE YES |
At any time to know your currnt edition:
SQL> col default_edition FOR a10 SQL> col session_edition FOR a10 SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') AS default_edition, SYS_CONTEXT('userenv', 'session_edition_name') AS session_edition FROM dual; DEFAULT_ED SESSION_ED ---------- ---------- ORA$BASE ORA$BASE |
To simulate structure change on a table between your different edition you have to implement editioning views. To populate figures onto those tables from different (linked) editions you have to implement what is called forward crossedition trigger (from pre to post) and reverse crossedition triggers (from post to pre).
To create the view that ideally have the same name as the table you need to rename your table and create a view based on it:
SQL> ALTER TABLE test01 RENAME TO test01_tab; TABLE altered. SQL> CREATE OR REPLACE editioning VIEW test01 AS SELECT id, first_name, last_name FROM test01_tab; VIEW created. SQL> DROP TRIGGER test01_first_last_name; TRIGGER dropped. SQL> CREATE OR REPLACE TRIGGER test01_first_last_name before INSERT OR UPDATE ON test01 FOR each ROW BEGIN :NEW.first_name:=INITCAP(:NEW.first_name); :NEW.last_name:=INITCAP(:NEW.last_name); END; TRIGGER created. SQL> ALTER PROCEDURE greet compile; PROCEDURE altered. |
Then I add a new column to test01_tab table. This could be done is a fully online fashion using DBMS_REDEFINITION:
SQL> ALTER TABLE test01_tab ADD full_name VARCHAR2(40) NOT NULL; ALTER TABLE test01_tab ADD full_name VARCHAR2(40) NOT NULL * ERROR AT line 1: ORA-01758: TABLE must be empty TO ADD mandatory (NOT NULL) COLUMN SQL> ALTER TABLE test01_tab ADD full_name VARCHAR2(40); TABLE altered. SQL> UPDATE test01_tab SET full_name=first_name||' '||last_name WHERE full_name IS NULL; 1 ROW updated. SQL> COMMIT; COMMIT complete. |
And all this just to be in a better initial shape. Notice the view that is editionable:
SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects; OBJECT_NAME OBJECT_TYPE E EDITION_NA ------------------------------ ----------------------- - ---------- TEST01_PK INDEX TEST01_FIRST_LAST_NAME TRIGGER Y ORA$BASE GREET PROCEDURE Y ORA$BASE TEST01_TAB TABLE TEST01 VIEW Y ORA$BASE |
Creating the new edition and new edition objects
If we acknowledge that our curernt release is v1 then let’s create a release v2 edition and switch to it:
SQL> CREATE edition releasev2 AS child OF ora$base; Edition created. SQL> ALTER SESSION SET edition=releasev2; SESSION altered. |
In this new edition I update all my applicative objects to match the new full_name column:
SQL> CREATE OR REPLACE editioning VIEW test01 AS SELECT id, first_name, last_name, full_name FROM test01_tab; VIEW created. SQL> CREATE OR REPLACE PROCEDURE greet(pid NUMBER) IS full_name VARCHAR2(40); BEGIN SELECT full_name INTO full_name FROM test01 WHERE id=pid; dbms_output.put_line('Hello ' || full_name); END; / PROCEDURE created. SQL> CREATE OR REPLACE TRIGGER test01_first_last_name before INSERT OR UPDATE ON test01 FOR each ROW BEGIN :NEW.first_name:=INITCAP(:NEW.first_name); :NEW.last_name:=INITCAP(:NEW.last_name); :NEW.full_name:=:NEW.first_name||' '||:NEW.last_name; END; / TRIGGER created. |
Let’s create the corssedition triggers. In my case I only need a forward crossedtion trigger to update the full_name column in case something is inserted in release v1 of my application (ora$base edition). Those crossed edition triggers must be created in releasev2 edition. My reverse crossedition trigger is empty because I have no business logic to handle in my test case. I have simply added it for reference of the syntax:
SQL> CREATE OR REPLACE TRIGGER test01_forward before INSERT OR UPDATE ON test01_tab FOR each ROW forward crossedition BEGIN :NEW.full_name:=INITCAP(:NEW.first_name)||' '||INITCAP(:NEW.last_name); END; / TRIGGER created. SQL> CREATE OR REPLACE TRIGGER test01_reverse before INSERT OR UPDATE ON test01_tab FOR each ROW REVERSE crossedition BEGIN NULL; END; / TRIGGER created. |
Testing the new edition and crossedition triggers
So now I have:
SQL> SELECT object_name, object_type, editionable, edition_name FROM user_objects_ae ORDER BY object_name, edition_name; OBJECT_NAME OBJECT_TYPE E EDITION_NA ------------------------------ ----------------------- - ---------- GREET PROCEDURE Y ORA$BASE GREET PROCEDURE Y RELEASEV2 TEST01 VIEW Y ORA$BASE TEST01 VIEW Y RELEASEV2 TEST01_FIRST_LAST_NAME TRIGGER Y ORA$BASE TEST01_FIRST_LAST_NAME TRIGGER Y RELEASEV2 TEST01_FORWARD TRIGGER Y RELEASEV2 TEST01_PK INDEX TEST01_REVERSE TRIGGER Y RELEASEV2 TEST01_TAB TABLE 10 ROWS selected. |
If I add a row in ora$base release:
SQL> ALTER SESSION SET edition=ora$base; SESSION altered. SQL> SELECT * FROM test01 ORDER BY id; ID FIRST_NAME LAST_NAME ---------- -------------------- -------------------- 1 Yannick Jaquier 2 Larry Ellison SQL> INSERT INTO test01(id,first_name,last_name) VALUES(3,'safra','katz'); 1 ROW created. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test01 ORDER BY id; ID FIRST_NAME LAST_NAME ---------- -------------------- -------------------- 1 Yannick Jaquier 2 Larry Ellison 3 Safra Katz SQL> ALTER SESSION SET edition=releasev2; SESSION altered. SQL> SELECT * FROM test01 ORDER BY id; ID FIRST_NAME LAST_NAME FULL_NAME ---------- -------------------- -------------------- ---------------------------------------- 1 Yannick Jaquier Yannick Jaquier 2 Larry Ellison Larry Ellison 3 Safra Katz Safra Katz |
Using service to point to desired edition
The default database edition is ora$base. If I connect with my account:
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') -------------------------------------------------------------------------------- ORA$BASE SQL> DESC test01; Name NULL? TYPE ----------------------------------------- -------- ---------------------------- ID NUMBER(38) FIRST_NAME NOT NULL VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(20) |
And change the default database edition:
SQL> ALTER DATABASE DEFAULT edition=releasev2; DATABASE altered. SQL> SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_EDITION'; PROPERTY_VALUE -------------------------------------------------------------------------------- RELEASEV2 |
If I reconnect with my account I directly land in releasev2 edition:
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') -------------------------------------------------------------------------------- RELEASEV2 SQL> DESC test01; Name NULL? TYPE ----------------------------------------- -------- ---------------------------- ID NUMBER(38) FIRST_NAME NOT NULL VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(20) FULL_NAME VARCHAR2(40) SQL> ALTER DATABASE DEFAULT edition=ora$base; DATABASE altered. |
Even if changing the database default edition is usable when everything is done in real life it is not that practical. You might want to go step by step and have a reduced bunch of users accessing the new version of your application while most part of your users is still using the previous release. Nowadays with containerized applications and the easiness to spin up a web server it would be really easy to have most of your user accessing the old legacy url, for example https://wwww.myapplication.com while some beta tester users would access something like https://next.myapplication.com. This can be achieved from Oracle database standpoint with the help of services where you can specify the target edition.
Your database will be split in different edition and will server different version of your application. In my example I have two but you are obviously not limited to two:
I create two services each of them targeting one of my two editions:
SQL> EXEC dbms_service.create_service(service_name => 'pdb1_v1', network_name => 'pdb1_v1', edition => 'ora$base'); PL/SQL PROCEDURE successfully completed. SQL> EXEC dbms_service.create_service(service_name => 'pdb1_v2', network_name => 'pdb1_v2', edition => 'releasev2'); PL/SQL PROCEDURE successfully completed. SQL> EXEC dbms_service.start_service(service_name => 'pdb1_v1'); PL/SQL PROCEDURE successfully completed. SQL> EXEC dbms_service.start_service(service_name => 'pdb1_v2'); PL/SQL PROCEDURE successfully completed. |
Then when using them with EZConnect method to avoid creating entries in tnsnames.ora file I land directly in the desired edition (ora$base being the default edition of the database):
SQL> CONNECT yjaquier/secure_password@//server01.domain.com:1541/pdb1_v1 Connected. SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') -------------------------------------------------------------------------------- ORA$BASE SQL> CONNECT yjaquier/secure_password@//server01.domain.com:1541/pdb1_v2 Connected. SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') -------------------------------------------------------------------------------- RELEASEV2 |
To complete the process you can make releasev2 the default database edition and drop ora$base edition with something like:
SQL> DROP edition ora$base CASCADE; Edition dropped. |
DBMS_REDEFINITION for online table redefinition
The small test case is to add a full_name column to my test01 test table. Is the operation possible with DBMS_REDEFINITION:
SQL> SET serveroutput ON SIZE 999999 SQL> EXEC dbms_redefinition.can_redef_table(uname=>'yjaquier', tname=>'test01'); PL/SQL PROCEDURE successfully completed. |
I create the target table with obviously a different name:
SQL> CREATE TABLE test01_int ( id INTEGER, first_name VARCHAR2(20) NOT NULL, last_name VARCHAR2(20) NOT NULL, full_name VARCHAR2(40) NOT NULL ); TABLE created. |
I start the redefinition, full_name will be a concatenation, with a space in the middle, of first_name and last_name:
SQL> EXEC dbms_redefinition.start_redef_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int', col_mapping=>'id id,first_name first_name,last_name last_name,first_name||'' ''||last_name full_name', refresh_dep_mviews=>'Y', enable_rollback=>TRUE); PL/SQL PROCEDURE successfully completed. |
Let’s insert a row to test:
SQL> INSERT INTO test01 VALUES(2,'larry','ellison'); 1 ROW created. SQL> COMMIT; COMMIT complete. SQL> SET lines 200 SQL> SELECT * FROM test01; ID FIRST_NAME LAST_NAME ---------- -------------------- -------------------- 1 Yannick Jaquier 2 Larry Ellison SQL> SELECT * FROM test01_int; ID FIRST_NAME LAST_NAME FULL_NAME ---------- -------------------- -------------------- ---------------------------------------- 1 Yannick Jaquier Yannick Jaquier |
Newly added row can be synchronize using:
SQL> EXEC dbms_redefinition.sync_interim_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int'); PL/SQL PROCEDURE successfully completed. SQL> SELECT * FROM test01_int; ID FIRST_NAME LAST_NAME FULL_NAME ---------- -------------------- -------------------- ---------------------------------------- 1 Yannick Jaquier Yannick Jaquier 2 Larry Ellison Larry Ellison |
Complete the redefinition process with:
SQL> DECLARE errors PLS_INTEGER; BEGIN dbms_redefinition.copy_table_dependents( uname => 'yjaquier', orig_table => 'test01', int_table => 'test01_int', copy_indexes => dbms_redefinition.cons_orig_params, -- change from non-default to cpy indexes copy_constraints => FALSE, num_errors => errors); dbms_output.put_line('Number of errors that occurred while cloning dependent objects: ' || errors); END; / SQL> -- Sync last figures SQL> EXEC dbms_redefinition.sync_interim_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int'); PL/SQL PROCEDURE successfully completed. SQL> -- Complete redefinition SQL> EXEC dbms_redefinition.finish_redef_table(uname=>'yjaquier', orig_table=>'test01', int_table=>'test01_int', disable_rollback=>TRUE); PL/SQL PROCEDURE successfully completed. SQL> DESC test01; Name NULL? TYPE ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- ID NUMBER(38) FIRST_NAME NOT NULL VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(20) FULL_NAME NOT NULL VARCHAR2(40) SQL> SELECT * FROM test01; ID FIRST_NAME LAST_NAME FULL_NAME ---------- -------------------- -------------------- ---------------------------------------- 1 Yannick Jaquier Yannick Jaquier 2 Larry Ellison Larry Ellison |
Without copy_constraints => false I got:
ERROR AT line 1: ORA-01442: COLUMN TO be modified TO NOT NULL IS already NOT NULL ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2615 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2615 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2053 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1914 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 457 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1907 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 2594 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 3811 ORA-06512: AT "SYS.DBMS_REDEFINITION", line 6556 ORA-06512: AT line 4 |
References
- Using Edition-Based Redefinition
- Online Table Redefinition (DBMS_REDEFINITION) Enhancements in Oracle Database 10g Release 1
- How to use COL_MAPPING parameter of DBMS_REDEFINITION.START_REDEF_TABLE (Doc ID 2525563.1)
- DBMS_REDEFINITION Revisited
- Edition-Based Redefinition (EBR)
- Edition Based Redefinition in Oracle Database 11g Release 2
