Oracle DBMS_DIMENSION
Version 19.3

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

Related Topics
Built-in Functions
Built-in Packages
Dimensions
{$ORACLE_HOME}/rdbms/admin/utldim.sql
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx