Can a table have multiple editioning views in Oracle RDBMS 11

Question

Can a table ABC have multiple editioning views in Oracle Database?

I have an editioning view ABC (created from table ABC) that shows the old edition to be OLD_EDITION_1 as its edition_name in the USER_OBJECTS table. I am trying to create a new editioning view named ABC with an edition name of NEW_EDITION_2.

Answer 1

You can only have one editioning view per table in each edition.

To explain, some background. Edition-based redefinition (EBR) brought two new schema objects:

• Editions
• Editioning views

Editions extend the namespace for an object. This prefixes the schema. So the fully-qualified name of an object in Oracle Database from 11.2 is:

edition_name.schema_name.object_name

But you can't explicitly reference the edition_name. It is part of your session settings. When you create an editionable object, it goes in your current edition.

For example, the following creates a new user, editions enables it, creates the table "abc" and the editioning view "ABC":

grant create session, create table, create view, unlimited tablespace to
 usr identified by usr;
alter user usr enable editions;
create edition e1;
grant use on edition e1 to usr;
conn usr/usr
create table "abc" (
 c1 int, 
 c2 int
);
create or replace editioning view abc as 
 select * from "abc";
select object_name, object_type, edition_name
from user_objects;
OBJECT_NAME OBJECT_TYPE EDITION_NAME 
abc TABLE <null> 
ABC VIEW ORA$BASE 

If you try and create a new version of the view in the edition e1, you'll get an error:

create or replace editioning view e1.abc as 
 select c1 from "abc";
ORA-01917: user or role 'E1' does not exist

This is because you're currently in the edition ora$base. The default for every database.

To create a new version of the view, you need to switch your session to edition e1:

alter session set edition = e1;
create or replace editioning view abc as 
 select * from "abc";
select object_name, object_type, edition_name
from user_objects;
OBJECT_NAME OBJECT_TYPE EDITION_NAME 
abc TABLE <null> 
ABC VIEW E1 
select object_name, object_type, edition_name
from user_objects_ae;
OBJECT_NAME OBJECT_TYPE EDITION_NAME 
abc TABLE <null> 
ABC VIEW E1 
ABC VIEW ORA$BASE 

As you see, you now have two views "ABC". One in each edition.

If, still in E1, you try and create a new editioning view using "abc", you'll get an error:

create or replace editioning view abc_new as 
 select * from "abc";
ORA-42300: an Editioning view is already defined on this table

Answer 2

Assuming you are on Oracle 11g release 2:

Your editioning view name and table name should not be the same. From https://blogs.oracle.com/oraclemagazine/edition-based-redefinition%2c-part-2 the sequence of events are:

1. Alter table ABC rename to ABC_RT;
2. create editioning view ABC as select /* column list here */ from ABC_RT;
3. Then create your new edition, make that edition the current edition, then create a new editioning view ABC.

You need to look at the data dictionary view USER_OBJECTS_AE also, instead of USER_OBJECTS