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: 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;