Oracle | DBMS_EDITIONS

Oracle DBMS_EDITIONS_UTILITIES Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

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;