....
"So in your case, you would implement this by having a second instance of the table(s) in question, and editioning views over each".
....
I think, Connor was suggesting something like this.
demo@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> grant create session ,
2 create procedure,
3 create trigger,
4 create view,
5 create any edition,
6 drop any edition,
7 create table,
8 create sequence,
9 create job
10 to demo;
Grant succeeded.
rajesh@ORA12C> alter user demo enable editions;
User altered.
rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> show edition
EDITION
------------------------------
ORA$BASE
demo@ORA12C> select * from emp;
EMPNO DEPTNO
---------- ----------
1 10
2 10
2 rows selected.
Let's say that as part of deployment, we need to add a couple of rows in EMP table which should **NOT** be visible to the application.
So you need a copy of table (emp2) along with an editioning view. ( Preparing the schema for EBR )
demo@ORA12C> create table emp2 as select * from emp;
Table created.
demo@ORA12C> rename emp to emp_tab;
Table renamed.
demo@ORA12C> create or replace
2 editioning view emp
3 as
4 select empno,deptno
5 from emp_tab ;
View created.
Creating the edition along with Forward edition trigger(so that each changes on EMP from application will be cascaded to EMP2)
demo@ORA12C> create edition E1 as child of ORA$BASE;
Edition created.
demo@ORA12C>
demo@ORA12C> alter session set edition=E1;
Session altered.
demo@ORA12C> create or replace trigger emp_fwdedition
2 before insert
3 on emp_tab
4 for each row
5 FORWARD CROSSEDITION
6 begin
7 insert into emp2(empno,deptno)
8 values (:new.empno,:new.deptno);
9 end emp_fwdedition;
10 /
Trigger created.
demo@ORA12C>
Now the changes in EMP from application side, will be cascaded to EMP2 (due to the presence of Forward edition trigger).
demo@ORA12C>
demo@ORA12C> alter session set edition=ORA$BASE;
Session altered.
demo@ORA12C> show edition
EDITION
------------------------------
ORA$BASE
demo@ORA12C> insert into emp(empno,deptno) values(3,10);
1 row created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> select * from emp;
EMPNO DEPTNO
---------- ----------
3 10
1 10
2 10
3 rows selected.
demo@ORA12C> alter session set edition=E1;
Session altered.
demo@ORA12C> show edition
EDITION
------------------------------
E1
demo@ORA12C> select * from emp2;
EMPNO DEPTNO
---------- ----------
1 10
2 10
3 10
3 rows selected.
demo@ORA12C>
Now as part of Deployment activities, we could connect to the new edition E1 and do record inserts on EMP2 - which will not be exposed/available to EMP object on BASE edition.
demo@ORA12C> show edition
EDITION
------------------------------
E1
demo@ORA12C> insert into emp2(empno,deptno) values(4,20);
1 row created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> select * from emp2;
EMPNO DEPTNO
---------- ----------
1 10
2 10
3 10
4 20
4 rows selected.
demo@ORA12C> alter session set edition=ORA$BASE;
Session altered.
demo@ORA12C> select * from emp;
EMPNO DEPTNO
---------- ----------
3 10
1 10
2 10
3 rows selected.
demo@ORA12C>
then as part of clean up activity, we could do the below steps.
drop trigger emp_fwdedition;
drop table emp_tab;
drop editioning view emp;
rename emp2 to emp;
drop edition ORA$BASE
Once EMP2 to rename to EMP, application can still access EMP and see those changes introduced as part of deployment/up-gradation process.