Oracle Editions
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  
Dependencies
ALL_EDITIONING_VIEWS CDB_EDITIONS DBA_SOURCE_AE
ALL_EDITIONING_VIEWS_AE CDB_EDITION_COMMENTS EDITION$
ALL_EDITIONS CDB_SOURCE_AE USER_EDITIONING_VIEWS
ALL_EDITION_COMMENTS DBA_EDITIONING_VIEWS USER_EDITIONING_VIEWS_AE
ALL_SOURCE_AE DBA_EDITIONING_VIEWS_AE USER_SOURCE_AE
CDB_EDITIONING_VIEWS CDB_EDITIONS _ACTUAL_EDITION_OBJ
CDB_EDITIONING_VIEWS_AE DBA_EDITION_COMMENTS _CURRENT_EDITION_OBJ
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 / as sysdba

ALTER USER uwclass ENABLE EDITIONS;

conn uwclass/uwclass

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@pdborcl as sysdba

ALTER USER scott ENABLE EDITIONS;
ALTER USER scott DISABLE EDITIONS FORCE;
 
Drop Edition
Drop Edition Object DROP EDITION <edition_name> [CASCADE];
conn / 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
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
Packages
Pipelined Table Functions
Procedures
Synonyms
Triggers
Types
Users

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