Oracle | DBMS_DIMENSION

Oracle DBMS_DIMENSION Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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