Edition Based Redefinition Demo

I’m a big fan of Edition-Based Redefinition that was introduced in Oracle Database 11gR2.

Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

Here’s a little demo I made that shows the advantages of editions…
——————————————————————————

/*
   #===============================================================================================================
   #                                                                                                                                              
   #         FILE: 11gR2_Database_Editions.sql
   #
   #        USAGE: Run in a test databsse/user schema
   #
   #  DESCRIPTION: Simple script to show database editioning features based off in large part by scripts from christian antognini & tom kyte
   #      OPTIONS:  
   # REQUIREMENTS: 
   #       AUTHOR: Matt D (https://oramatt.wordpress.com)
   #      CREATED: 7/22/2010
   #      VERSION: 1.0
   #
   #===============================================================================================================
*/





set echo off
clear screen
prompt "##############################################################"
prompt "What is the user that you was to enable editioning? "
accept youruser
prompt "##############################################################"
prompt "What is the password for your user?"
accept yourpassword
prompt "##############################################################"
set echo on
prompt "##############################################################"
connect / as sysdba
grant dba, connect, resource to &youruser identied by &yourpassword; 
alter user &youruser enable editions; 
pause


connect &youruser/&yourpassword
/*set autot on explain; */
set echo on 
set lines 200

alter session set edition = ora$base; 
pause


drop edition release_2 cascade; 
pause
drop edition release_1 cascade; 
pause

clear screen

/* setup the environment */

create edition release_1 as child of ora$base; 
pause
create edition release_2 as child of release_1; 
pause


/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


/* create the employees table, sequence and insert trigger */

create table employees_tab (
  id number(10) primary key, 
  first_name varchar2(100), 
  last_name varchar2(100),
  email varchar2(100)
); 
pause

create sequence employee_seq; 
pause


/* go to release_1 */

alter session set edition = release_1; 
pause


/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause

create editioning view employees as
select id, first_name as firstname, last_name as lastname, email
from employees_tab; 
pause


create or replace trigger employees_bi_trg
	before insert on employees for each row
begin
	select employee_seq.nextval into :new.id from dual;
end employees_bi_trg;
/
pause


/* insert some data */
insert into employees (firstname, lastname, email)
values ('matt','dee','mattdee@oramatt.wordpress.com'); 
pause

commit;

pause


/* redefine table */


alter table employees_tab add (
  email_recipient varchar2(100), 
  email_domain varchar2(100)
); 

pause


alter session set edition = release_2; 
pause

/* check what edition you are in */

column edition_name format a20
select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


/* create the employees editioning view in release_2 */

create or replace editioning view employees as
select id, first_name as firstname, last_name as lastname, email_recipient, email_domain
from employees_tab; 
pause


/* create and apply forward crossedition trigger */
/* this trigger populates the table for release_2 */
/* making switching between releases easy */ 

create trigger employees_fwd_trg
  before insert or update on employees_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; 
/

pause

alter trigger employees_fwd_trg enable; 
pause

/* update employee records */
/* this SHOULD FAIL!!! */
/* this is because release_2 doesn't have an email only column */
update employees set email = email ;
commit;

/* see how the data looks */
/* the new columns SHOULD be EMPTY as we haven't updated the data from release_1 yet */
column firstname format a20
column lastname format a20
column email_recipient format a20
column email_domain format a20
select * from employees;
pause



/* see how the data looks */

alter session set edition = release_1;
pause

/* check what edition you are in */

column edition_name format a20
select sys_context('userenv','session_edition_name') edition_name from dual; 
pause

column firstname format a20
column lastname format a20
column email format a20
select * from employees; 
pause

/* update the employee records */
update employees set email = email;
commit;
pause

select *
from employees; 
pause

commit;


alter session set edition = release_2; 
pause

/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


column firstname format a20
column lastname format a20
column email_recipient format a20
column email_domain format a20
select * from employees; 
pause


pause


/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


/* create reverse crossedition trigger */
/* this trigger populates the table for release_1 */
/* making switching between releases easy */

create trigger employees_rev_trg
  before insert or update on employees_tab for each row
  reverse crossedition 
  disable
begin
  :new.email := :new.email_recipient || '@' || :new.email_domain; 
end; 
/  

pause

alter trigger employees_rev_trg enable; 

pause

insert into employees values ('','tim','smith','tim.smith','gmail.com'); 
pause

commit; 

pause


alter session set edition = release_1; 
pause

/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


select *
from employees; 
pause

insert into employees (firstname, lastname, email)
values ('scott','tiger','scott.tiger@gmail.com'); 
commit;
pause


select *
from employees; 
pause



alter session set edition = release_2;
pause


/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause

select *
from employees; 

pause

prompt "ALL DONE!!!"


/*  cleanup */

drop table employees_tab purge; 
drop view employees; 
drop sequence employee_seq; 

alter session set edition = ora$base; 

*/

/* check what edition you are in */

select sys_context('userenv','session_edition_name') edition_name from dual; 
pause


drop edition release_2 cascade; 
drop edition release_1 cascade; 


set echo off

3 responses to “Edition Based Redefinition Demo”

victor feinstein

March 27, 2014

wanted to ask if anyone who is using Oracle EBR in a big way?
we have a lot of non-editionable types, not just tables, but also TYPES in the database, I have AQ queues and tables, function-based indexes, etc.
I want to make sure also that it’s ok to never purge your editions?
Can someone just keeps adding to the editions tree over time? say, 20 new editions each year?
as it’s not going to be possible to purge prior editions, if they all inherit from ORA$BASE to begin with? Is this true?

thanks!

vfeinstein50

April 13, 2014

I have difficulty purging prior editions that clutter up my system: we deploy new code every 2 weeks, hence a new edition every time:

ORA$BASE –> v37 –> v38 for example.

I actualized all objects from ORA$BASE to v37, and for v38, same thing, actualized all v37 into v38

then i tried this:

DROP EDITION v37 cascade;

and no luck.. got this:
SQL Error: ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child

Any words of wisdom?

1. mattdee
  
  April 13, 2014
  
  you can’t drop v37 because v38 depends on it…

OraMatt: YABAOracle

Edition Based Redefinition Demo

3 responses to “Edition Based Redefinition Demo”

Leave a reply to vfeinstein50 Cancel reply

Navigation

About

Edition Based Redefinition Demo

Share this:

3 responses to “Edition Based Redefinition Demo”

Leave a reply to vfeinstein50 Cancel reply

Navigation

About