Oracle | DBMS_EDITIONS

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	COMPARE_EDITION	SET_NULL_COLUMN_VALUES_TO_EXPR
CLEAN_UNUSABLE_EDITIONS	SET_EDITIONING_VIEWS_READ_ONLY

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;