Oracle Editioning Views
Version 11.2.0.3
 
General Information
NOTE: 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.
View Related Data Dictionary Objects
obj$  view$  viewcon$
dba_editioning_view_cols all_editioning_view_cols user_editioning_view_cols
dba_editioning_view_cols_ae all_editioning_view_cols_ae user_editioning_view_cols_ae
dba_editioning_views all_editioning_views user_editioning_views
dba_editioning_views_ae all_editioning_views_ae user_editioning_views_ae
dba_errors_ae all_errors_ae user_errors_ae
dba_objects_ae all_objects_ae user_objects_ae
dba_tab_cols all_tab_cols user_tab_cols
dba_updatable_columns all_updatable_columns user_updatable_columns
dba_views all_views user_views
System Privileges
create view create any 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;
 
Create Force
Force ... 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
Alter 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
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-2013 Daniel A. Morgan All Rights Reserved