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
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%';
