Oracle Editions
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose  
Dependencies
ALL_EDITIONING_VIEWS DBA_EDITIONING_VIEW_COLS KU$_EDITION_SCHEMAOBJ_VIEW
ALL_EDITIONING_VIEWS_AE DBA_EDITIONING_VIEW_COLS_AE KU$_EDITION_TRIG_EXISTS_VIEW
ALL_EDITIONING_VIEW_COLS CDB_EDITIONS KU$_USER_EDITIONING_LIST_T
ALL_EDITIONING_VIEW_COLS_AE DBA_EDITION_COMMENTS KU$_USER_EDITIONING_T
ALL_EDITIONS DBA_SOURCE_AE KU$_USER_EDITIONING_VIEW
ALL_EDITION_COMMENTS DBMS_EDITIONS_UTILITIES USABLE_EDITIONS
ALL_SOURCE_AE DBMS_EDITIONS_UTILITIES_LIB USER_EDITIONED_TYPES
CDB_EDITIONED_TYPES EDITION$ USER_EDITIONING$
CDB_EDITIONING_VIEWS EDITIONING_TYPES$ USER_SOURCE_AE
CDB_EDITIONING_VIEWS_AE USER_EDITIONING_VIEWS USER_EDITIONING_VIEWS
CDB_EDITIONING_VIEW_COLS USER_EDITIONING_VIEWS_AE USER_EDITIONING_VIEWS_AE
CDB_EDITIONING_VIEW_COLS_AE FED$EDITIONS USER_EDITIONING_VIEW_COLS
CDB_EDITIONS GV$EDITIONABLE_TYPES USER_EDITIONING_VIEW_COLS_AE
CDB_EDITION_COMMENTS GV_$EDITIONABLE_TYPES V$EDITIONABLE_TYPES
CDB_SOURCE_AE I_USER_EDITIONING V_$EDITIONABLE_TYPES
DBA_EDITIONED_TYPES KU$_EDITION_OBJ_VIEW _ACTUAL_EDITION_OBJ
DBA_EDITIONING_VIEWS KU$_EDITION_PROC_EXISTS_VIEW _CURRENT_EDITION_OBJ
DBA_EDITIONING_VIEWS_AE    
Object Privileges USE
GRANT use ON EDITION ora$base TO public;
REVOKE use ON EDITION ora$base FROM public;
System Privileges
ALTER ANY EDITION CREATE ANY EDITION DROP ANY EDITION
Editionable Object Types
FUNCTIONS PACKAGES TYPES & TYPE BODIES
LIBRARYS PROCEDURES VIEWS
Non-Editionable Object Types
Functions used to create function based indexes
Segments
Types used to define object-tables: For example a VARRAY used as a table column
 
Create Edition
Create Edition

Oracle recommends that the AS CHILD OF syntax not be used
CREATE EDITION <child_edition_name> [AS CHILD OF <parent_edition_name>];
conn sys@pdbdev as sysdba

ALTER USER uwclass ENABLE EDITIONS;

conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE test_proc AUTHID CURRENT_USER IS
BEGIN
  NULL;
END test_proc;
/

SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 2,1;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 2,1;

desc user_source

desc user_source_ae

SELECT * FROM all_editions;

CREATE EDITION
demo_ed;

SELECT * FROM all_editions;
PL/SQL Object Editioning sho edition

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

-- created in default edition
CREATE OR REPLACE FUNCTION edition_test RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN 'ORA$BASE';
END edition_test;
/

SELECT edition_test FROM dual;

ALTER SESSION SET EDITION = demo_ed;

sho edition

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

SELECT edition_test FROM dual;

-- created in the new edition
CREATE OR REPLACE FUNCTION edition_test RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN sys_context('USERENV', 'CURRENT_EDITION_NAME');
END edition_test;
/

SELECT edition_test FROM dual;

ALTER SESSION SET EDITION = ora$base;

SELECT edition_test FROM dual;

DROP FUNCTION edition_name;

SELECT edition_name FROM dual;

ALTER SESSION SET EDITION = ora$base;

SELECT edition_name FROM dual;
 
Alter Edition
Alter Edition Object ALTER EDITION <edition_name> <USABLE | UNUSABLE>;
See DROP EDITION Demo
 
Commenting An Edition
Adding a comment to an edition COMMENT ON EDITION <edition_name> IS '<comment_text>';
col comments format a50

SELECT * FROM all_edition_comments;

COMMENT EDITION demo_ed IS 'This is a demonstration edition';

SELECT * FROM all_edition_comments;

COMMENT EDITION demo_ed IS '';

SELECT * FROM all_edition_comments;
 
Edition Enable Users
Enable a user to access and work with editions ALTER USER <user_name> <ENABLE | DISABLE> EDITIONS [FORCE];
conn sys@pdbdev as sysdba

ALTER USER scott ENABLE EDITIONS;
ALTER USER scott DISABLE EDITIONS FORCE;
 
Drop Edition
Drop Edition Object DROP EDITION <edition_name> [CASCADE];
conn sys@pdbdev as sysdba

CREATE EDITION uwed3;

DROP EDITION uwed3;

ALTER EDITION uwed3 UNUSABLE;

DROP EDITION uwed3 CASCADE;
 
Edition Related Queries
Object Information conn / as sysdba

desc obj$

set linesize 121

SELECT obj#, name, namespace, type#, status, spare3
FROM obj$
WHERE name = 'ORA$BASE';
Default Edition Information conn / as sysdba

desc props$

set linesize 121
col value$ format a30
col comment$ format a40

SELECT *
FROM props$
WHERE name LIKE '%EDITION%';
Edition Related Auditing conn / as sysdba

desc audit_actions

SELECT *
FROM audit_actions
WHERE name LIKE '%EDITION%';

Related Topics
Built-in Functions
Built-in Packages
CrossEdition Triggers
DBMS_EDITIONS_UTILITIES
DBMS_SESSION
DBMS_SQL
Editioning Demo 1
Editioning Demo 2
Editioning Demo 3
Editioning Demo 4
Editioning Demo 5
Editioning Demo 6
Editioning Demo 7
Editioning Views
Functions
Object Tables
Pipelined Table Functions
Procedures
Synonyms
Triggers
Types
Users
What's New In 12cR2
What's New In 18cR3

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