Oracle | DBMS_EDITIONS

Oracle DBMS_EDITIONS_UTILITIES Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Utilities to enhance working with databases making use of Edition Based Redefinition.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Edition Relationship Constants
IDENTICAL	INTEGER	0
ANCESTOR	INTEGER	1
DESCENDENT	INTEGER	2
UNRELATED	INTEGER	3

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_EDITIONS_UTILITIES' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_EDITIONS_UTILITIES ORDER BY 1;

Returns 81 objects

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
{ORACLE_HOME}/rdbms/admin/prvtedu.plb

Subprograms

ACTUALIZE_ALL	COMPARE_EDITION	SET_NULL_COLUMN_VALUES_TO_EXPR
CLEAN_UNUSABLE_EDITIONS	SET_EDITIONING_VIEWS_READ_ONLY

ACTUALIZE_ALL

Actualize all objects in the current edition dbms_editions_utilities.actualize_all;

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 INTEGER, ed2objn IN INTEGER) RETURN INTEGER;

conn sys@pdbdev as sysdba

      

      CREATE EDITION 'demo$ed';

      

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

      

      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;