Oracle DBMS_DIMENSION
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_SUMVDM
ALL_DIM_ATTRIBUTES ALL_DIM_LEVEL_KEY DBMS_SYS_ERROR
ALL_DIM_CHILD_OF DBMS_OUTPUT DBMS_UTILITY
ALL_DIM_HIERARCHIES DBMS_STANDARD PRVT_DIMENSION_SYS_UTIL
ALL_DIM_JOIN_KEY DBMS_SUMMARY USER_TABLES
Documented Yes
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@pdborcl 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 8i and 9i databases.

Overload 1
dbms_dimension.validate_dimension(
dimension_name  IN VARCHAR2,
dimension_owner IN VARCHAR2,
incremental     IN BOOLEAN,
check_nulls     IN BOOLEAN := FALSE);
conn sys@pdborcl as sysdba

exec dbms_dimension.validate_dimension('TIMES_DIM', 'SH', TRUE, TRUE);
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 2
dbms_dimension.validate_dimension(
dimension    IN VARCHAR2,
incremental  IN BOOLEAN := TRUE,
check_nulls  IN BOOLEAN := FALSE,
statement_id IN VARCHAR2 := NULL);
conn sys@pdborcl 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;

Related Topics
Dimensions
Packages
/rdbms/admin/utldim.sql

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-2014 Daniel A. Morgan All Rights Reserved