Oracle Editioning Views
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Editioning views are the second tier of the Edition Based Redefinition architecture. They must be preceded by edition enabling the user and may be optionally followed by the creation of cross-edition triggers. This page demonstrates both what can, and can not, be done with editioning views. That said it is still far from complete and the reader is advised to thoroughly review the docs at http://tahiti.oracle.com an far more importantly to download a copy of Bryn Llewellyn's Editioning White Paper.

What is most critical to understand is that due to the tight restrictions on what is permissible in an Editioning View there are no performance penalties for accessing a table through an editioning view, rather than directly: And there never will be.
Dependencies
ALL_EDITIONING VIEW_COLS CDB_OBJECTS_AE OBJ$
ALL_EDITIONING VIEW_COLS_AE CDB_TAB_COLS USER_EDITIONING VIEW_COLS
ALL_EDITIONING VIEWS CDB_UPDATABLE_COLUMNS USER_EDITIONING VIEW_COLS_AE
ALL_ERRORS_AE CDB_VIEWS USER_EDITIONING VIEWS
ALL_OBJECTS_AE DBA_EDITIONING VIEW_COLS USER_ERRORS_AE
ALL_TAB_COLS DBA_EDITIONING VIEW_COLS_AE USER_OBJECTS_AE
ALL_UPDATABLE_COLUMNS DBA_EDITIONING VIEWS USER_TAB_COLS
ALL_VIEWS DBA_ERRORS_AE USER_UPDATABLE_COLUMNS
CDB_EDITIONING VIEW_COLS DBA_OBJECTS_AE USER_VIEWS
CDB_EDITIONING VIEW_COLS_AE DBA_TAB_COLS VIEW$
CDB_EDITIONING VIEWS DBA_UPDATABLE_COLUMNS VIEWCON$
CDB_ERRORS_AE DBA_VIEWS  
System Privileges
CREATE ANY VIEW CREATE VIEW DROP ANY VIEW
 
Create
Create Single Table Editioning View CREATE OR REPLACE [[NO] FORCE] EDITIONING VIEW <view_name> AS <select_statement>;
CREATE TABLE ebr_tab (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW reg_view AS
SELECT *
FROM ebr_tab;

CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT *
FROM ebr_tab;

SELECT * FROM reg_view;

SELECT * FROM ed_view;

SELECT view_name, editioning_view
FROM user_views;

SELECT * FROM user_editioning_views_ae;

desc dba_editioning_view_cols_ae

SELECT view_name, view_column_name, table_column_name, edition_name
FROM dba_editioning_view_cols_ae;
Forces creation of a view even when the view will be invalid. NO FORCE is the default CREATE OR REPLACE [[NO] FORCE] EDITIONING VIEW <view_name> AS <select_statement>;
CREATE OR REPLACE FORCE EDITIONING VIEW ed_view AS
SELECT *
FROM ebr_tab;
 
Alter
Recompile an invalid editioning view ALTER VIEW <view_name> COMPILE;
ALTER VIEW ed_view COMPILE;
 
Drop
Drop Editioning View DROP VIEW <view_name>;
ALTER VIEW ed_view;
 
Editioning View Restrictions
Note The following demos are designed to create errors to illustrate both what does, and what does not, work. Examine each carefully before trying first the version on the left then the version on the right. There are many more restrictions than those demonstrated below. Be sure to consult the docs for restrictions before creating an Editioning View.
Description Regular View Editioning View
ORA-42305: An editioning view can only replace an editioning view CREATE OR REPLACE VIEW test_view1 AS
SELECT *
FROM ebr_tab;

CREATE OR REPLACE VIEW test_view1 AS
SELECT *
FROM ebr_tab;
CREATE OR REPLACE VIEW test_view1 AS
SELECT *
FROM ebr_tab;

CREATE OR REPLACE EDITIONING VIEW test_view1 AS
SELECT *
FROM ebr_tab;
SELECT clause alternation not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT UPPER(testcol) AS uptcol
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT UPPER(testcol) AS uptcol
FROM ebr_tab;
Multi-table Joins not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT e.testcol, d.dummy
FROM ebr_tab e, dual d;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT e.testcol, d.dummy
FROM ebr_tab e, dual d;
WHERE clause filter not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
WHERE rownum < 11;

CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
WHERE testcol LIKE 'A%';
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
WHERE rownum < 11;

CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
WHERE testcol LIKE 'A%';
GROUP BY clause not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol, COUNT(*) CNT
FROM ebr_tab
GROUP BY testcol;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol, COUNT(*) CNT
FROM ebr_tab
GROUP BY testcol;
ORDER BY clause not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
ORDER BY 1;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
ORDER BY 1;

DISTINCT/UNIQUE not allowed
CREATE OR REPLACE VIEW reg_view AS
SELECT DISTINCT testcol
FROM ebr_tab;

CREATE OR REPLACE VIEW reg_view AS
SELECT UNIQUE testcol
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT DISTINCT testcol
FROM ebr_tab;

CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT UNIQUE testcol
FROM ebr_tab;
Concatenation not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol || 'A' AS TESTCOL
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol || 'A' AS TESTCOL
FROM ebr_tab;
Duplicate columns not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol TESTCOLA, testcol TESTCOLB
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol TESTCOLA, testcol TESTCOLB
FROM ebr_tab;

Set operators not allowed
CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
UNION ALL
SELECT testcol
FROM ebr_tab;

CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
INTERSECT
SELECT testcol
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
UNION ALL
SELECT testcol
FROM ebr_tab;

CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
INTERSECT
SELECT testcol
FROM ebr_tab;
Multiple editioning views per table not allowed CREATE OR REPLACE VIEW reg_view2 AS
SELECT testcol
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view2 AS
SELECT testcol
FROM ebr_tab;
ORA-1702: Creating editioning views on regular views is not allowed CREATE OR REPLACE VIEW reg_view AS
SELECT *
FROM ebr_tab;

CREATE OR REPLACE EDITIONING VIEW ed_view3 AS
SELECT testcol
FROM reg_view;
 
ORA-42304: Creating editioning views in the schema that does not own the table is not allowed GRANT ALL ON ebr_tab TO UWCLASS;

conn uwclass/uwclass

CREATE OR REPLACE VIEW reg_view AS
SELECT *
FROM ebradmin.ebr_tab;
GRANT ALL ON ebr_tab TO UWCLASS;

conn uwclass/uwclass

CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT *
FROM ebradmin.ebr_tab;
Column renaming is allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol AS something_else
FROM ebr_tab;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol AS something_else
FROM ebr_tab;

Table style triggers are supported

Requires the CREATE TRIGGER system
privilege be granted
CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab;

CREATE OR REPLACE TRIGGER view_trig
BEFORE UPDATE
ON reg_view
FOR EACH ROW
BEGIN
  NULL;
END view_trig;
/
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab;

CREATE OR REPLACE TRIGGER view_trig
BEFORE UPDATE
ON ed_view
FOR EACH ROW
BEGIN
  NULL;
END view_trig;
/
READ ONLY clause is allowed CREATE OR REPLACE VIEW reg_view AS
SELECT testcol
FROM ebr_tab
READONLY;
CREATE OR REPLACE EDITIONING VIEW ed_view AS
SELECT testcol
FROM ebr_tab
READONLY;

LOCK TABLE is allowed
SELECT sys_context('USERENV', 'SID')
FROM dual;

SELECT * FROM v$lock
WHERE sid=138;

LOCK TABLE reg_view IN SHARE MODE NOWAIT;

-- as sys
SELECT * FROM v$lock
WHERE sid=138;

COMMIT;
SELECT sys_context('USERENV', 'SID')
FROM dual;

SELECT * FROM v$lock
WHERE sid=138;

LOCK TABLE ed_view IN SHARE MODE NOWAIT;

-- as sys
SELECT * FROM v$lock
WHERE sid=138;

COMMIT;

Related Topics
CrossEdition Triggers
DBMS_EDITIONS_UTILITIES
Editioning Demo 1
Editioning Demo 2
Editioning Demo 3
Editioning Demo 4
Editioning Demo 5
Editioning Demo 6
Editioning Demo 7
Editions
Views

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved