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