ORACLE: Edition-Based Redefinition or EBR and errors (ORA-42300,ORA-38802,ORA-38807)

Edition-Based Redefinition

Looks like nice introduction of versioning in the database. Still researching, bear with me on details:

Reference Sample Commands:

alter database default edition =ORA$BASE;#default no need  to execute, only needed if a differnet version becomes default.
create user scott   identified by scott; #optional if schema exists
grant create session,create procedure to scott;#optional if schema exists with privs
create edition version1 as child of ora$base;
alter user scott enable editions;
 grant use on edition version1 to scott;
 grant use on edition version1 to <ANYSCHEMA>;

 Very imp : alter session set edition = version1;

Experiment one: Only editioned views

1. Create one versioned view on base(default).

2. Created another versioned view  on version created in step 0.

select USERNAME,EDITIONS_ENABLED from dba_users;
USERNAME                       E
------------------------------ -
MGMT_VIEW                      N
SYS                            N
SYSTEM                         N
SCOTT                          Y   <===Enabled editions for this schema
SPACE                          N

create edition version1 as child of ora$base; #I created as sysdba

SQL> alter session set edition =VERSION1;

Session altered.

SQL> create editioning view v1_emp as select empno,ename from scott.emp;

View created.

SQL> select object_name,object_type,status,edition_name  from user_objects;

OBJECT_NAME                    OBJECT_TYPE         STATUS  EDITION_NAME

------------------------------ ------------------- ------- ------------------------------

XEMP                           TABLE               VALID

SALGRADE                       TABLE               VALID

BONUS                          TABLE               VALID

PK_EMP                         INDEX               VALID

EMP                            TABLE               VALID

DEPT                           TABLE               VALID

PK_DEPT                        INDEX               VALID

V1_EMP                         VIEW                VALID   VERSION1  ******

SQL> create edition version2 as child of version1;

Edition created.

SQL> alter session set edition = version2;

Session altered.

SQL> create editioning view v2_dept as select * from scott.dept;

View created.

SQL> select object_name,object_type,status,edition_name  from user_objects;

OBJECT_NAME                                                                                                           OBJECT_TYPE          STATUS  EDITION_NAME

-------------------------------------------------------------------------------------------------------------------------------- ------------------- ------- ------------------------------

XEMP                                                                                                                  TABLE                VALID

SALGRADE                                                                                                              TABLE                VALID

BONUS                                                                                                                 TABLE                VALID

PK_EMP                                                                                                                INDEX                VALID

EMP                                                                                                                   TABLE                VALID

DEPT                                                                                                                  TABLE                VALID

PK_DEPT                                                                                                               INDEX                VALID

V1_EMP                                                                                                                VIEW                 VALID   VERSION1

V2_DEPT                                                                                                               VIEW                 VALID   VERSION2

Errors encountered and explaination:

1. SQL> create edition version1 as child of ora$base;

create edition version1 as child of ora$base

*

ERROR at line 1:

ORA-38807: Implementation restriction: an edition can have only one child

Only one child version allowed. 

eg: ORA$BASE can have child V1, V1 can have V2, V2 can have V3 and so on but V1 cannot have 2 child V2 & V3(parallel versions)

2. SQL> alter session set edition = version1;

ERROR:

ORA-38802: edition does not exist

Version must be create before use. required the following:

create edition version1 as child of ora$base;

alter user scott enable editions;#Once only

3.SQL> alter session set edition = version2;

Session altered.

SQL> create editioning view v2_emp as select empno,ename,job from scott.emp;

create editioning view v2_emp as select empno,ename,job from scott.emp

                                                                   *

ERROR at line 1:

ORA-42300: an Editioning view is already defined on this table

Once editioned view V1_emp is created, another on the same table cannot be created.