Oracle DBMS_EDITIONS_UTILITIES
Version 21c

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 Utilities to enhance working with databases making use of Edition Based Redefinition.
AUTHID CURRENT_USER
Dependencies
ALL_ALL_TABLES KU$_20_1_PIOTABLE_VIEW KU$_NT_PARENT_VIEW
ALL_EDITIONING_VIEWS_AE KU$_ACPTABLE_VIEW KU$_P2TCOLUMN_VIEW
ALL_OBJECTS KU$_CLUSTER_VIEW KU$_P2TPARTCOL_VIEW
ALL_VIEWS_AE KU$_COLUMN_VIEW KU$_PARTITION_VIEW
DBMS_ASSERT KU$_FHTABLE_VIEW KU$_PCOLUMN_VIEW
DBMS_EDITIONS_UTILITIES_LIB KU$_HTABLE_VIEW KU$_PFHTABLE_VIEW
DBMS_OUTPUT KU$_IOTABLE_VIEW KU$_PHTABLE_VIEW
DBMS_PRIV_CAPTURE KU$_M_VIEW_FH_VIEW KU$_PIOTABLE_VIEW
DBMS_SQL KU$_M_VIEW_H_VIEW KU$_PRIM_COLUMN_VIEW
DBMS_STANDARD KU$_M_VIEW_IOT_VIEW KU$_QTAB_STORAGE_VIEW
KU$_10_1_FHTABLE_VIEW KU$_M_VIEW_LOG_FH_VIEW KU$_QUEUE_TABLE_VIEW
KU$_10_1_HTABLE_VIEW KU$_M_VIEW_LOG_H_VIEW KU$_SP2TCOLUMN_VIEW
KU$_10_1_IOTABLE_VIEW KU$_M_VIEW_LOG_PFH_VIEW KU$_SP2TPARTCOL_VIEW
KU$_10_1_PFHTABLE_VIEW KU$_M_VIEW_LOG_PH_VIEW KU$_SUBPARTITION_VIEW
KU$_10_1_PHTABLE_VIEW KU$_M_VIEW_PFH_VIEW KU$_VIEW_VIEW
KU$_10_1_PIOTABLE_VIEW KU$_M_VIEW_PH_VIEW KU$_ZM_VIEW_FH_VIEW
KU$_10_2_FHTABLE_VIEW KU$_M_VIEW_PIOT_VIEW KU$_ZM_VIEW_H_VIEW
KU$_11_2_VIEW_VIEW KU$_M_VIEW_VIEW KU$_ZM_VIEW_IOT_VIEW
KU$_19_1_M_VIEW_FH_VIEW KU$_M_ZONEMAP_FH_VIEW KU$_ZM_VIEW_PFH_VIEW
KU$_20_1_ACPTABLE_VIEW KU$_M_ZONEMAP_H_VIEW KU$_ZM_VIEW_PH_VIEW
KU$_20_1_FHTABLE_VIEW KU$_M_ZONEMAP_IOT_VIEW KU$_ZM_VIEW_PIOT_VIEW
KU$_20_1_HTABLE_VIEW KU$_M_ZONEMAP_PFH_VIEW USABLE_EDITIONS
KU$_20_1_IOTABLE_VIEW KU$_M_ZONEMAP_PH_VIEW USER_SYS_PRIVS
KU$_20_1_PFHTABLE_VIEW KU$_M_ZONEMAP_PIOT_VIEW UTL_RECOMP
KU$_20_1_PHTABLE_VIEW    
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-00942 Missing Table
ORA-38802 Edition does not exist
ORA-38803 Edition is unusable
ORA-38804 Not a legal edition name
ORA-38805 Edition is in use
ORA-38806 Edition is being altered or dropped
ORA-38807 Implementation restriction: an edition can have only one child
ORA-38808 Edition has a usable parent and a usable child
ORA-38809 Edition must be unusable
ORA-38810 Implementation restriction: can not drop an edition that has a parent and a child
ORA-38811 Need CASCADE option to drop edition that has actual objects
ORA-38812 Maximum number of editions reached
ORA-38813 editions not supported for schema <schema_name>
ORA-38814 ALTER SESSION SET EDITION must be first statement of transaction
ORA-38815 ALTER SESSION SET EDITION must be a top-level SQL statement
ORA-38816 Edition has a child that inherits objects from the edition
ORA-38817 Insufficient Privileges
ORA-54002 Only pure functions can be specified in a virtual column expression
First Available 11.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsedu.sql
Subprograms
 
ACTUALIZE_ALL (new 21c)
Actualize all objects in the current edition dbms_editions_utilities.actualize_all;
PRAGMA SUPPLEMENTAL_LOG_DATA(Actualize_All, AUTO);
exec dbms_editions_utilities.actualize_all;

PL/SQL procedure successfully completed.
 
CLEAN_UNUSABLE_EDITIONS
Formally drops covered objects in unusable editions, and drops empty unusable editions if possible dbms_editions_utilities.clean_unusable_editions;
exec dbms_editions_utilities.clean_unusable_editions;

PL/SQL procedure successfully completed.
 
COMPARE_EDITION
Compares two given editions to determine their parent/child relation dbms_editions_utilities.compare_edition(
ed1objn IN NUMBER,
ed2objn IN NUMBER)
RETURN NUMBER;
conn sys@pdbdev as sysdba

CREATE EDITION 'demo$ed';

SELECT dbms_editions_utilities.compare_edition('ORA$BASE', 'DEMO$ED')
FROM dual;

DROP EDITION demo$ed;
 
SET_EDITIONING_VIEWS_READ_ONLY
Given the table name, set all the Editioning views in all editions to read-only or read write dbms_editions_utilities.set_editioning_views_read_only(
table_name IN VARCHAR2,
owner      IN VARCHAR2 DEFAULT NULL,
read_only  IN BOOLEAN  DEFAULT TRUE);
SELECT *
FROM dba_editions;

desc dba_views

SELECT view_name, editioning_view, read_only
FROM dba_views
WHERE owner = 'SH';

set long 1000000

SELECT text
FROM dba_views
WHERE owner = 'SH'
AND view_name = 'PROFITS';

exec dbms_editions_utilities.set_editioning_views_read_only('SALES', 'SH', TRUE);
 
SET_NULL_COLUMN_VALUES_TO_EXPR
Replaces null values in a replacement column with the value of an expression. The expression evaluation cost is deferred to future updates and queries. dbms_editions_utilities.set_null_column_values_to_expr(
table_name  IN VARCHAR2,
column_name IN VARCHAR2,
expression  IN VARCHAR2);
conn sys@pdbdev as sysdba

ALTER USER uwclass ENABLE EDITIONS;

conn uwclass/uwclass@pdbdev

CREATE TABLE test_t (
sal      NUMBER,
comm     NUMBER,
tot_comp NUMBER);

CREATE OR REPLACE EDITIONING VIEW test AS
SELECT * FROM test_t;

INSERT INTO test (sal, comm) VALUES (1, 1);
INSERT INTO test (sal, comm) VALUES (2, 2);
INSERT INTO test (sal, comm) VALUES (3, 3);
COMMIT;

DECLARE
 cNULLStr CONSTANT VARCHAR2(30) := 'SAL + COMM';
BEGIN
  dbms_editions_utilities.set_null_column_values_to_expr('TEST_T', 'TOT_COMP', cNULLStr);
END;
/

SELECT * FROM test;

SELECT * FROM test_t;

Related Topics
Built-in Functions
Built-in Packages
CrossEdition Triggers
DBMS_EDITIONS_UTILITIES2
Editions
Editioning 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