Oracle DBMS_CUBE_ADVISE
Version 21c

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 Cube Organized ADvise (COAD) table function mv_cube_advice produces a table of advice records containing constraint DDL to enhance query rewrite opportunities for a cube MV. In-line not null, primary/forign key, relational dimensions and mv logs can be generated. It also defines public table and record types that the mv_cube_advice function returns.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Advice Statements Types
DBMS_COAD_ADVTYP_NN BINARY_INTEGER 1
DBMS_COAD_ADVTYP_PKT BINARY_INTEGER 2
DBMS_COAD_ADVTYP_PKV BINARY_INTEGER 3
DBMS_COAD_ADVTYP_FKT BINARY_INTEGER 4
DBMS_COAD_ADVTYP_FKV BINARY_INTEGER 5
DBMS_COAD_ADVTYP_RELDIM BINARY_INTEGER 6
DBMS_COAD_ADVTYP_MVLOG BINARY_INTEGER 7
DBMS_COAD_ADVTYP_MVCMP BINARY_INTEGER 8
 Trace Diagnostics Destinations
DBMS_COAD_DIAG_NOTRACE BINARY_INTEGER 0
DBMS_COAD_DIAG_SRVROUT BINARY_INTEGER 1
DBMS_COAD_DIAG_TRCFILE BINARY_INTEGER 2
 Trace Diagnostics Log Entry Types
DBMS_COAD_DIAG_NOTE BINARY_INTEGER 0
DBMS_COAD_DIAG_BACKTRACE BINARY_INTEGER 1
DBMS_COAD_DIAG_CKMVPRIV BINARY_INTEGER 2
DBMS_COAD_DIAG_HANDLED BINARY_INTEGER 3
Data Types SUBTYPE qref2_vc2 IS VARCHAR2(261); /* '"128"."128"' */
SUBTYPE qref3_vc2 IS VARCHAR2(392); /* '"128"."128"."128"' */

-- Record and ref cursor type for input to table function get_atr_expr_rc()
TYPE lvlList_r IS RECORD (
dimOwner DBMS_ID,
dimName  DBMS_ID,
lvlName  DBMS_ID);

TYPE lvlList_t IS REF CURSOR RETURN lvlList_r;

-- Record and table type for output from table function get_atr_expr_rc()
TYPE atrExprList_r IS RECORD (
dimOwner DBMS_ID,
dimName  DBMS_ID,
lvlName  DBMS_ID,
atrExpr  qref3_vc2);

TYPE atrExprList_t IS TABLE OF atrExprList_r;
Dependencies
ALL_CONSTRAINTS ALL_MVIEW_DETAIL_RELATIONS DBMS_AW_LIB
ALL_CONS_COLUMNS ALL_MVIEW_JOINS DBMS_CUBE
ALL_CUBE_DIMENSIONALITY ALL_MVIEW_KEYS DBMS_CUBE_ADVISE_SEC
ALL_CUBE_DIMENSIONS ALL_MVIEW_LOGS DBMS_OUTPUT
ALL_CUBE_DIM_LEVELS ALL_OBJECTS DBMS_PRIV_CAPTURE
ALL_CUBE_DIM_VIEW_COLUMNS ALL_TAB_COLS DBMS_STANDARD
ALL_CUBE_HIER_LEVELS COAD$CUBE_MVIEWS DBMS_STATS_INTERNAL
ALL_DIMENSIONS COAD$INLINE_NOTNULL_CONS DBMS_SYS_ERROR
ALL_DIM_JOIN_KEY COAD$MVIEWS_WITH_VIEWS DBMS_UTILITY
ALL_DIM_LEVELS COAD_ADVICE_RECT DUAL
ALL_DIM_LEVEL_KEY COAD_ADVICE_ PLITBLM
ALL_MVIEWS DBMS_ASSERT PRVT_ADVISOR
ALL_MVIEW_AGGREGATES    
Documented No
First Available Not known
Pragma PRAGMA supplemental_log_data(default, READ_ONLY);
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/olap/admin/prvtcoas.plb
{ORACLE_HOME}/olap/admin/dbmscoad.sql
{ORACLE_HOME}/olap/admin/prvtcoad.plb
Subprograms
 
CHECK_FOR_NESTING
Tests if MV has non-merged nested views dbms_cube_advise.check_for_nesting(
mvOwner IN VARCHAR2,
mvName  IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
GET_ATR_EXPR_RC
Returns a list of attribute expressions for each level when given a cursor of type lvlList_t i.e.dimension levels dbms_cube_advise.get_atr_expr_rc(lvlList in lvlList_t) RETURN atrExprList_t PIPELINED;
TBD
 
GET_COLDISTINCTCOUNT
Get count of distinct values in colName dbms_cube_advise.get_colDistinctCount(
owner   IN VARCHAR2,
tabName IN VARCHAR2,
colName IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_DIMHIERJOIN_DISPOSITION
Gets conflicting object info for hierarchy snowflake joins, if any dbms_cube_advise.get_dimHierJoin_disposition(
tabOwner IN VARCHAR2,
tabName  IN VARCHAR2,
colName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_DIMLVL_DISPOSITION
Gets conflicting object info for dimension level mappings, if any dbms_cube_advise.get_dimlvl_disposition(
tabOwner IN VARCHAR2,
tabName  IN VARCHAR2,
colName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_DIM_DISPOSITION
Gets conflicting object info for dimension name, if any dbms_cube_advise.get_dim_disposition(
dimOwner IN VARCHAR2,
dimName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_FK_NAME
Gets name of table column foreign key constraint, if any dbms_cube_advise.get_fk_name(
tabOwner IN VARCHAR2,
tabName  IN VARCHAR2,
colName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_LVL_NAME
Gets a level name for a given dimension and column alias dbms_cube_advise.get_lvl_name(
owner   IN VARCHAR2,
dimName IN VARCHAR2,
colName IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_MEAS_COL
Get first measure column for given MV column alias dbms_cube_advise.get_meas_col(
mvOwner  IN VARCHAR2,
mvName   IN VARCHAR2,
colAlias IN VARCHAR2 )
RETURN VARCHAR2;
TBD
 
GET_MVNAME
Function to get MV name for a given cube or cube dimension object dbms_cube_advise.get_MVName(
owner   IN VARCHAR2,
objName IN VARCHAR2,
objHier IN VARCHAR2 DEFAULT NULL,
mvType  IN VARCHAR2 DEFAULT 'REFRESH' )
RETURN VARCHAR2;
TBD
 
GET_NN_NAME
Gets name of table column in-line not null constraint, if any dbms_cube_advise.get_nn_name(
tabOwner IN VARCHAR2,
tabName  IN VARCHAR2,
colName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_PK_NAME
Gets name of table column primary key constraint, if any dbms_cube_advise.get_pk_name(
tabOwner IN VARCHAR2,
tabName  IN VARCHAR2,
colName  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
IS_MD_CLASS
Returns true if API objName has a colName that matches and is then
* mdClass. MEASURE, UNIQUEKEYATTRIBTE, or ANY
dbms_cube_advise.is_md_class(
mdClass IN BINARY_INTEGER,
owner   IN VARCHAR2,
objName IN VARCHAR2,
colName IN VARCHAR2)
RETURN BINARY_INTEGER ;
TBD
 
LOG
Produced dbms_output messages based on the msgids dbms_cube_advise.log(
msgid  IN BINARY_INTEGER DEFAULT 0,
msgtxt IN VARCHAR2       DEFAULT '');
TBD
 
MV_CUBE_ADVICE
Generates records that include a CLOB containing DDL and DML that allows the broadest range of query rewrite transforms possible and mv log based fast refresh for the cube based MVs dbms_cube_advise.mv_cube_advise(
owner    IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_USER'),
objName  IN VARCHAR2,
reqType  IN VARCHAR2 DEFAULT '0',
validate IN NUMBER   DEFAULT 0)
RETURN coad_advice_t PIPELINED;
TBD
 
SET_CNS_EXCEPTION_LOG
Set the name of an EXCEPTIONS table

The table must be manually created first by running the DDL in utlxexcpt.sql
dbms_cube_advise.set_cns_exception_log(exceptLogTab IN VARCHAR2 DEFAULT '"' || sys_context('USERENV', 'CURRENT_USER') || '"."EXCEPTIONS"');
SQL>@?/rdbms/admin/utlexcpt.sql

Table created.

desc exceptions
Name         Null?  Type
------------ ------ ----------------
ROW_ID              ROWID
OWNER               VARCHAR2(128)
TABLE_NAME          VARCHAR2(128)
CONSTRAINT          VARCHAR2(128)


exec dbms_cube_advise.set_cns_exception_log('SYS.EXCEPTIONS');

PL/SQL procedure successfully completed.
 
TRACE
Sets dbms_coad_diag level flag. Allows diagnostics messages to go to serveroutput via dbms_output dbms_cube_advise.trace(diagLevel IN BINARY_INTEGER);
exec dbms_cube_advise.trace(1);
DBMS_COAD_DIAG: Changing diagLevel from [0] to [1]

PL/SQL procedure successfully completed.


exec dbms_cube_advise.trace(0);
DBMS_COAD_DIAG: Changing diagLevel from [1] to [0]

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_CUBE
DBMS_CUBE_ADVISE_SEC
DBMS_CUBE_EXP
DBMS_CUBE_LOG
DBMS_CUBE_UTIL
What's New In 19c
What's New In 20c-21c

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