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 will be available on generic Linux platforms in January and soon on AIX and Windows

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;