Oracle DBMS_HIERARCHY
Version 19.2.0.1

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 Analytic view validation
AUTHID CURRENT_USER
Constants
Name Data Type Value
Upgrade Log Table
VERSION_12_2_0_1 NUMBER 1
VERSION_12_2_0_2 NUMBER 2
VERSION_NONE NUMBER 3
VERSION_LATEST NUMBER VERSION_12_2_0_2
Dependencies
DBMS_ASSERT DBMS_HCS_LIB  
Documented Yes
Exceptions
Error Code Reason
ORA-00942 TABLE_DOES_NOT_EXIST
ORA-00955 NAME_ALREADY_USED
ORA-18263 MISMATCH_OBJ_LOGNUM
ORA-18275 MISMATCH_COL_LENGTH
ORA-18276 LOG_TABLE_UPGRADE
First Available 12.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source $ORACLE_HOME/rdbms/admin/dbmshier.sql
Subprograms
 
CREATE_VALIDATE_LOG_TABLE
Creates a log table for use in validating hierarchies dbms_hierarchy.create_validate_log_table(
table_name       IN VARCHAR2,
owner_name       IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
ignore_if_exists IN BOOLEAN  DEFAULT FALSE);
exec dbms_hierarchy.create_validate_log_table('UWLOGTAB', 'UWCLASS', TRUE);
 
GET_MV_SQL_FOR_AV_CACHE (new 19c)
Undocumented dbms_hierarchy.get_mv_sql_for_av_cache(
analytic_view_name       IN VARCHAR2,
cache_idx                IN NUMBER, -- 0 based cache index
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN CLOB;
TBD
 
IS_NUMERIC (new 19c)
Returns 1 if the string can be converted to an integer or decimal value, otherwise 0 dbms_hierarchy.is_numeric(strnum IN VARCHAR2)
RETURN NUMBER;
SQL> SELECT dbms_hierarchy.is_numeric('20')
  2  FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20')
-------------------------------
                              1

SQL> SELECT dbms_hierarchy.is_numeric('20.6')
  2  FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20.6')
---------------------------------
                                1

SQL> SELECT dbms_hierarchy.is_numeric('20A6')
  2  FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20A6')
---------------------------------
                                0
 
UPGRADE_VALIDATE_LOG_TABLE (new 19c)
Undocumented dbms_hierarchy.upgrade_validate_log_table(
table_name IN VARCHAR2,
owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'));
exec dbms_hierarchy.upgrade_validate_log_table('UWLOGTAB', 'UWCLASS');
 
VALIDATE_ANALYTIC_VIEW
Validates an analytic view writing output to the named log table dbms_hierarchy.validate_analytic_view(
analytic_view_name       IN VARCHAR2,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_table_name           IN VARCHAR2 DEFAULT NULL,
log_table_owner_name     IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN NUMBER;
exec dbms_hierarchy.validate_analytic_view('UWAVIEW', 'UWCLASS', 'UWLOGTAB', 'UWCLASS', TRUE);
 
VALIDATE_CHECK_SUCCESS
Undocumented dbms_hierarchy.validate_check_success(
topobj_name          IN VARCHAR2,
topobj_owner         IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_number           IN NUMBER,
log_table_name       IN VARCHAR2 DEFAULT NULL,
log_table_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN VARCHAR2;
TBD
 
VALIDATE_HIERARCHY
Validate a hierarchy writing output to the named log table dbms_hierarchy.validate_hierarchy(
hier_name            IN VARCHAR2,
hier_owner_name      IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_table_name       IN VARCHAR2,
log_table_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN NUMBER;
TBD

Related Topics
Built-in Functions
Built-in Packages
Analytic Views
What's New In 18cR3
What's New In 19cR2

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