Oracle Editioning Views
Version 20c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 online docs and 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.
Dependencies
ALL_EDITIONING VIEW_COLS CDB_OBJECTS_AE OBJ$
ALL_EDITIONING VIEW_COLS_AE CDB_TAB_COLS USABLE_EDITIONS
ALL_EDITIONING VIEWS CDB_UPDATABLE_COLUMNS USER_EDITIONING VIEW_COLS
ALL_EDITIONING_VIEWS_AE CDB_VIEWS USER_EDITIONING VIEW_COLS_AE
ALL_EDITIONS DBA_EDITIONING VIEW_COLS USER_EDITIONING VIEWS
ALL_ERRORS_AE DBA_EDITIONING VIEW_COLS_AE USER_EDITIONING_VIEWS_AE
ALL_OBJECTS_AE DBA_EDITIONING VIEWS USER_ERRORS_AE
ALL_TAB_COLS DBA_EDITIONING_VIEWS_AE USER_OBJECTS_AE
ALL_UPDATABLE_COLUMNS DBA_EDITIONS 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_EDITIONING_VIEWS_AE DBA_VIEWS _ACTUAL_EDITION_OBJ
CDB_EDITIONS EDITION$ _CURRENT_EDITION_OBJ
CDB_ERRORS_AE KU$_EDITION_OBJ_VIEW  
Exceptions
Error Code Reason
ORA-42314 Editioning view cannot be owned by a non-editioned user
System Privileges
CREATE ANY VIEW CREATE VIEW DROP ANY VIEW
 
Create
Create Single Table Editioning View CREATE OR REPLACE [[NO] FORCE]
<EDITIONING | EDITIONABLE [EDITIONING] | NONEDITIONABLE>
VIEW [<schema_name>.]<view_name>
[SHARING = <DATA | EXTENDED DATA | METADATA | NONE]
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;
In an Application Root Sharing Data CREATE OR REPLACE <EDITIONING VIEW | EDITIONABLE [EDITIONING] | NONEDITIONABLE>
[<schema_name>.]<view_name>
[SHARING = <METADATA | DATA | EXTENDED DATA | NONE]
AS <select_statement>;
conn uwadmin/uwadmin@uwapp_root

ALTER PLUGGABLE DATABASE APPLICATION uw_app
BEGIN UPGRADE '1.0' TO '1.1'
COMMENT 'Adding New Table With Sharing';

CREATE TABLE uwadmin.servers
SHARING=METADATA (
srvr_id NUMBER(10),
network_id NUMBER(10),
status VARCHAR2(1),
latitude FLOAT(20),
longitude FLOAT(20),
netaddress VARCHAR2(15));

INSERT INTO UWADMIN.SERVERS VALUES (1,1028,'Y',32.9806,-117.2567,'172.020.130.002');
COMMIT;

CREATE OR REPLACE EDITIONING VIEW uwadmin.data_share
SHARING=DATA
AS SELECT * FROM uwadmin.servers;

ALTER PLUGGABLE DATABASE APPLICATION uw_app END UPGRADE;
 
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 conn uwclass/uwclass@pdbdev

CREATE OR REPLACE VIEW test_view1 AS
SELECT *
FROM ebr_tab;

CREATE OR REPLACE VIEW test_view1 AS
SELECT *
FROM ebr_tab;		 conn uwclass/uwclass@pdbdev

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@pdbdev

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

conn uwclass/uwclass@pdbdev

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
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx