Oracle | DBMS_DIMENSION

Oracle DBMS_DIMENSION 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 Enables verification of dimension relationships and provides an alternative to the Enterprise Manager Dimension Wizard for displaying a dimension definition

AUTHID CURRENT_USER

Dependencies

ALL_DIMENSIONS	ALL_DIM_LEVELS	DBMS_SUMMARY
ALL_DIM_ATTRIBUTES	ALL_DIM_LEVEL_KEY	DBMS_SUMVDM
ALL_DIM_CHILD_OF	ALL_TABLES	DBMS_SYS_ERROR
ALL_DIM_HIERARCHIES	DBMS_OUTPUT	DBMS_UTILITY
ALL_DIM_JOIN_KEY	DBMS_STANDARD	PRVT_DIMENSION_SYS_UTIL

Documented Yes: Packages and Types Reference

Exceptions

Error Name	Reason
dimensionnotfound	The specified dimension was not found

First Available Created 16-OCT-1997

Related System Privileges

ALTER ANY DIMENSION

CREATE ANY DIMENSION

DROP ANY DIMENSION

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmssum.sql

Subprograms

DESCRIBE_DIMENSION

VALIDATE_DIMENSION

DESCRIBE_DIMENSION

Prints out the definition of the input dimension, including dimension owner and name, levels, hierarchies, attributes

This script uses the objects built with {$ORACLE_HOME}/rdbms/admin/ utldim.sql dbms_dimension.describe_dimension(dimension IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      desc sh.times_dim

      

      set serveroutput on

      

      exec dbms_dimension.describe_dimension('SH.TIMES_DIM');

VALIDATE_DIMENSION

Verifies that the relationships specified in a DIMENSION are correct. Offending rowids are stored in advisor repository. This overload is for compatibility with 10g and newer databases.

Overload 1 dbms_dimension.validate_dimension( dimension IN VARCHAR2, incremental IN BOOLEAN := TRUE, check_nulls IN BOOLEAN := FALSE, statement_id IN VARCHAR2 := NULL);

conn sys@pdbdev as sysdba

      

      exec dbms_dimension.validate_dimension('SH.TIMES_DIM', TRUE, TRUE, 'validate dimension demo');

      

      @$ORACLE_HOME/rdbms/admin/utldim.sql

      

      desc dimension_exceptions

      

      exec dbms_dimension.validate_dimension('SH.TIMES_DIM', TRUE, TRUE, 'validate dimension demo');

      

      SELECT COUNT(*)

      FROM dimension_exceptions;

Verifies that the relationships specified in a DIMENSION are correct. Offending rowids are stored in advisor repository. This overload is for compatibility with 8i and 9i databases.

Overload 2 dbms_dimension.validate_dimension( dimension_name IN VARCHAR2, check_nulls IN BOOLEAN := FALSE, statement_id IN VARCHAR2 := NULL);

conn sys@pdbdev as sysdba

      

      exec dbms_dimension.validate_dimension('TIMES_DIM',  TRUE);