Oracle DBMS_DIMENSION
Version 19.3

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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 18cR3
What's New In 19cR3

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