Oracle | DBMS_CUBE

Oracle DBMS_CUBE_LOG Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides interfaces which control logging in the OLAP infrastructure

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Build
BUILD_V11106	BINARY_INTEGER	1
BUILD_V11106A	BINARY_INTEGER	2
BUILD_V11107	BINARY_INTEGER	3
BUILD_V11200B2	BINARY_INTEGER	4
BUILD_V11200B3	BINARY_INTEGER	5
BUILD_V11200	BINARY_INTEGER	6
BUILD_VCURRENT	BINARY_INTEGER	BUILD_V12201
Dimension Compile
DIMENSION_COMPILE_V112ALPHA	BINARY_INTEGER	1
DIMENSION_COMPILE_V112	BINARY_INTEGER	2
DIMENSION_COMPILE_VCURRENT	BINARY_INTEGER	DIMENSION_COMPILE_V122
Log Full Record when no ROWID
FULL_RECORD_AUTO	BINARY_INTEGER	0
FULL_RECORD_ALWAYS	BINARY_INTEGER	1
FULL_RECORD_NEVER	BINARY_INTEGER	2
Log Levels (implemented as a function)
LEVEL_LOWEST_C	BINARY_INTEGER	1
LEVEL_LOW_C	BINARY_INTEGER	2
LEVEL_MEDIUM_C	BINARY_INTEGER	3
LEVEL_HIGH_C	BINARY_INTEGER	4
LEVEL_HIGHEST_C	BINARY_INTEGER	5
VERBOSE_ACTION_C	BINARY_INTEGER	LEVEL_LOWEST_C
VERBOSE_NOTICE_C	BINARY_INTEGER	LEVEL_LOW_C
VERBOSE_INFO_C	BINARY_INTEGER	LEVEL_MEDIUM_C
VERBOSE_STATS	BINARY_INTEGER	LEVEL_HIGH_C
VERBOSE_DEBUG	BINARY_INTEGER	LEVEL_HIGHEST_C
Log Progress
LOG_EVERY_N	BINARY_INTEGER	4
ALLOW_ERRORS	BINARY_INTEGER	5
MAX_REJECT_LOBS	BINARY_INTEGER	6
CONTINUE_AFTER_MAX_REJECTS	BINARY_INTEGER	7
CONTINUE_AFTER_MAX_NO	BINARY_INTEGER	0
CONTINUE_AFTER_MAX_YES	BINARY_INTEGER	1
Log Table Versions
OPERATIONS_V112ALPHA	BINARY_INTEGER	1
OPERATIONS_V112	BINARY_INTEGER	2
OPERATIONS_VCURRENT	BINARY_INTEGER	OPERATIONS_V112
Log Targets (implemented as a function)
TARGET_TABLE_C	BINARY_INTEGER	1
TARGET_TRACE_C	BINARY_INTEGER	2
TARGET_FILE_C	BINARY_INTEGER	3
TARGET_LOB_C	BINARY_INTEGER	4
Log Types (implemented as a function)
TYPE_OPERATIONS_C	BINARY_INTEGER	1
TYPE_REJECTED_RECORDS_C	BINARY_INTEGER	2
TYPE_DIMENSION_COMPILE_C	BINARY_INTEGER	3
TYPE_BUILD_C	BINARY_INTEGER	4
Miscellaneous
MAX_ERRORS	BINARY_INTEGER	1
FLUSH_INTERVAL	BINARY_INTEGER	2
LOG_FULL_RECORD	BINARY_INTEGER	3
Rejected Records
REJECTED_RECORDS_V112ALPHA	BINARY_INTEGER	1
REJECTED_RECORDS_V112	BINARY_INTEGER	2
REJECTED_RECORDS_VCURRENT	BINARY_INTEGER	REJECTED_RECORDS_V122

Data Types -- Types for create_reject_sql

TYPE reject_ids IS VARRAY(500) OF NUMBER; TYPE reject_sql IS VARRAY(500) OF CLOB;

Dependencies

ALL_TABLES	DBMS_AW	DBMS_OUTPUT
ALL_TAB_COLUMNS	DBMS_AW_LIB	DBMS_UTILITY
DBMS_ASSERT	DBMS_LOB	PLITBLM

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-37561	INVALID_TYPE
ORA-37562	INVALID_TARGET
ORA-37563	INVALID_LEVEL
ORA-37564	INVALID_VERSION
ORA-37566	INVALID_LOCATION
ORA-37571	INVALID_SQL_ID
ORA-37572	INVALID_ID
ORA-37573	NO_LIMITS
ORA-37577	INVALID_LOG_MSG_NAME

First Available 11.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/olap/admin/dbmscbl.sql

Subprograms

COMPLETE_OPLOG	LEVEL_LOWEST	TARGET_TRACE
CREATE_REJECT_SQL	LEVEL_MEDIUM	TYPE_BUILD
DEFAULT_NAME	SET_LOG_SPEC	TYPE_DIMENSION_COMPILE
DISABLE	SET_PARAMETER	TYPE_OPERATIONS
ENABLE	SET_QUERY_ENV	TYPE_REJECTED_RECORDS
FLUSH	START_OPLOG	VERBOSE_ACTION
GET_LOG	TABLE_CREATE	VERBOSE_DEBUG
GET_LOG_SPEC	TABLE_DROP	VERBOSE_INFO
GET_PARAMETER	TABLE_TRUNCATE	VERBOSE_NOTICE
LEVEL_HIGH	TARGET_FILE	VERBOSE_STATS
LEVEL_HIGHEST	TARGET_LOB	VERSION
LEVEL_LOW	TARGET_TABLE	WRITE_TO_OPLOG

COMPLETE_OPLOG

Undocumented logging function dbms_cube_log.complete_oplog(oplogHandleId IN NUMBER);

TBD

CREATE_REJECT_SQL

Create SQL to find rejected records. Given a schema, rejected records log table name and and an array of ID numbers, this returns an array of sql statements (1 per ID) that can be used to help find the rejected records. dbms_cube_log.create_reject_sql( schema IN VARCHAR2, logTableName IN VARCHAR2, inIds IN REJECT_IDS DEFAULT NULL) RETURN REJECT_SQL;

TBD

DEFAULT_NAME

Returns the default name for a logging type dbms_cube_log.default_name(log_type IN BINARY_INTEGER DEFAULT dbms_cube_log.type_operations_c) RETURN VARCHAR2;

DECLARE

       defname VARCHAR2(100);

      BEGIN

        defname := dbms_cube_log.default_name;

      END;

      /

DISABLE

Disable logging to a location dbms_cube_log.disable( log_type IN BINARY_INTEGER DEFAULT NULL, log_target IN BINARY_INTEGER DEFAULT NULL);

BEGIN

        dbms_cube_log.disable(dbms_cube_log.type_operations_c, dbms_cube_log.target_table_c);

      END;

      /

ENABLE

Enable logging to a specific location with a given level

Overload 1 dbms_cube_log.enable( log_type IN BINARY_INTEGER DEFAULT NULL, log_target IN BINARY_INTEGER DEFAULT NULL, log_level IN BINARY_INTEGER DEFAULT NULL);

conn sh/sh@pdbdev

      

      BEGIN

        dbms_cube_log.table_create(DBMS_CUBE_LOG.TYPE_BUILD, 'SH_CUBE_LOG');

        dbms_cube_log.enable(dbms_cube_log.type_operations_c, dbms_cube_log.target_table_c,  dbms_cube_log.verbose_action_c);

      END;

      /

Overload 2 dbms_cube_log.enable( log_type IN BINARY_INTEGER DEFAULT NULL, log_target IN BINARY_INTEGER DEFAULT NULL, log_level IN BINARY_INTEGER DEFAULT NULL, log_location IN OUT NOCOPY CLOB);

conn sh/sh@pdbdev

      

      DECLARE

       log_loc CLOB;

      BEGIN

        dbms_cube_log.table_create(dbms_cube_log.type_build_c, 'SH_CUBE_LOG');

        dbms_cube_log.enable(dbms_cube_log.type_rejected_records_c, dbms_cube_log.target_trace_c,

        dbms_cube_log.verbose_info, log_loc);

        dbms_output.put_line(log_loc);

      END;

      /

Overload 3 dbms_cube_log.enable( log_type IN BINARY_INTEGER DEFAULT NULL, log_target IN BINARY_INTEGER DEFAULT NULL, log_level IN BINARY_INTEGER DEFAULT NULL, log_location IN VARCHAR2);

conn sh/sh@pdbdev

      

      DECLARE

       log_loc VARCHAR2(100) := 'SH.SH_CUBE_LOG';

      BEGIN

        dbms_cube_log.table_create(dbms_cube_log.type_build_c, 'SH_CUBE_LOG');

        dbms_cube_log.enable(dbms_cube_log.type_build_c, dbms_cube_log.target_file_c, dbms_cube_log.verbose_stats, log_loc);

      END;

      /

FLUSH

Force any open logs to flush dbms_cube_log.flush;

exec dbms_cube_log.flush;

GET_LOG

Get current logging information dbms_cube_log.get_log( log_type IN BINARY_INTEGER DEFAULT NULL, log_target IN BINARY_INTEGER DEFAULT NULL, log_level OUT BINARY_INTEGER, log_location OUT VARCHAR2);

DECLARE

       log_lvl BINARY_INTEGER;

       log_loc VARCHAR2(100);

      BEGIN

        dbms_cube_log.get_log(ltyp, ltar, log_lvl, log_loc);

        dbms_output.put_line(log_lvl);

        dbms_output.put_line(log_loc);

      END;

      /

GET_LOG_SPEC

Retrieve string describing current logging dbms_cube_log.get_log_spec RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       logspec VARCHAR2(100);

      BEGIN

        logspec := dbms_cube_log.get_log_spec;

        dbms_output.put_line(logspec);

        dbms_cube_log.set_query_env(?);

        dbms_output.put_line(logspec);

        logspec := dbms_cube_log.get_log_spec;

        dbms_output.put_line(logspec);

      END;

      /

GET_PARAMETER

Retrieve a parameter's value dbms_cube_log.get_parameter( log_type IN BINARY_INTEGER DEFAULT dbms_cube_log.type_operations_c, log_parameter IN BINARY_INTEGER) RETURN BINARY_INTEGER;

DECLARE
log_parm BINARY_INTEGER;
BEGIN
log_parm := dbms_cube_log.get_parameter(dbms_cube_log.type_operations_c,
dbms_output.put_line(log_parm);
END;
/

LEVEL_HIGH

Log types function dbms_cube_log.level_high RETURN BINARY_INTEGER;

SELECT dbms_cube_log.level_high

      FROM dual;

LEVEL_HIGHEST

Log types function dbms_cube_log.level_high RETURN BINARY_INTEGER;

SELECT dbms_cube_log.level_highest

      FROM dual;

LEVEL_LOW

Log types function dbms_cube_log.level_low RETURN BINARY_INTEGER;

SELECT dbms_cube_log.level_low

      FROM dual;

LEVEL_LOWEST

Log types function dbms_cube_log.level_lowest RETURN BINARY_INTEGER;

SELECT dbms_cube_log.level_lowest

      FROM dual;

LEVEL_MEDIUM

Log types function dbms_cube_log.level_medium RETURN BINARY_INTEGER;

SELECT dbms_cube_log.level_medium

      FROM dual;

SET_LOG_SPEC

Set all logging based on string dbms_cube_log.set_log_spec(log_spec IN VARCHAR2);

exec dbms_cube_log.set_log_spec('OPERATIONS(TRACE) REJECTED_RECORDS(TABLE)');

SET_PARAMETER

Set a parameter's value dbms_cube_log.set_parameter( log_type IN BINARY_INTEGER DEFAULT dbms_cube_log.type_operations_c, log_parameter IN BINARY_INTEGER, value IN BINARY_INTEGER);

BEGIN

        dbms_cube_log.set_parameter(dbms_cube_log.type_rejected_records_c, 1, 150);

      END;

      /

SET_QUERY_ENV

Set all limits for query environment dbms_cube_log.set_query_env( sql_id IN VARCHAR2, id IN NUMBER DEFAULT NULL, tblname IN VARCHAR2 DEFAULT NULL);

See GET_LOG_SPEC Demo

START_OPLOG

Undocumented logging function dbms_cube_log.start_oplog( oplogHandleId IN NUMBER DEFAULT NULL, msgName IN VARCHAR2, -- Cannot be null msgText IN VARCHAR2 DEFAULT NULL, component IN VARCHAR2 DEFAULT 'PLSQL', operation IN VARCHAR2 DEFAULT NULL, -- Cannot be null (read your code Oracle) recordLogLevel IN BINARY_INTEGER DEFAULT LEVEL_LOW_C);

TBD

TABLE_CREATE

Creates an appropriate table for the given log type dbms_cube_log.table_create( log_type IN BINARY_INTEGER DEFAULT dbms_cube_log.type_operations_c, tblname IN VARCHAR2 DEFAULT NULL);

exec dbms_cube_log.table_create(dbms_cube_log.type_build_c, 'SH_CUBE_LOG');

      

      desc cube_build_log

      

      SELECT sequence_name FROM user_sequences;

TABLE_DROP (new 21c)

Given a schema name and table name drops the identified table dbms_cube_log.table_drop(tbl_name IN VARCHAR2);

See TABLE_TRUNCATE Demo Below

TABLE_TRUNCATE (new 21c)

Given a schema name and table name truncates the identified table dbms_cube_log.table_truncate(tbl_name IN VARCHAR2);

CREATE TABLE t (testcol DATE);

      

      INSERT INTO t (testcol) VALUES (SYSDATE);

      1 row created.

      

      COMMIT;

      

      Commit complete;

      

      exec dbms_cube_log.table_truncate(USER || '.T');

      

      PL/SQL procedure successfully completed.

      

      SELECT * FROM t;

      

      no rows selected

      

      exec dbms_cube_log.table_drop(USER || '.T');

      

      PL/SQL procedure successfully completed.

      

      SELECT * FROM t

      *

      ERROR at line 1:

      ORA-00942: table or view does not exist

TARGET_FILE

Log targets function dbms_cube_log.target_file RETURN BINARY_INTEGER;

SELECT dbms_cube_log.target_file

      FROM dual;

TARGET_LOB

Log targets function dbms_cube_log.target_lob RETURN BINARY_INTEGER;

SELECT dbms_cube_log.target_lob

      FROM dual;

TARGET_TABLE

Log targets function dbms_cube_log.target_table RETURN BINARY_INTEGER;

SELECT dbms_cube_log.target_table

      FROM dual;

TARGET_TRACE

Log targets function dbms_cube_log.target_trace RETURN BINARY_INTEGER;

SELECT dbms_cube_log.target_trace

      FROM dual;

TYPE_BUILD

Log type function dbms_cube_log.type_build RETURN BINARY_INTEGER;

SELECT dbms_cube_log.type_build

      FROM dual;

TYPE_DIMENSION_COMPILE

Log type function dbms_cube_log.type_dimension-compile RETURN BINARY_INTEGER;

SELECT dbms_cube_log.type_dimension_compile

      FROM dual;

TYPE_OPERATIONS

Log type function dbms_cube_log.type_operations RETURN BINARY_INTEGER;

SELECT dbms_cube_log.type_operations

      FROM dual;

TYPE_REJECTED_RECORDS

Log type function dbms_cube_log.type_rejected_records RETURN BINARY_INTEGER;

SELECT dbms_cube_log.type_rejected_records

      FROM dual;

VERBOSE_ACTION

Log level function dbms_cube_log.verbose_action RETURN BINARY_INTEGER;

SELECT dbms_cube_log.verbose_action

      FROM dual;

VERBOSE_DEBUG

Log level function dbms_cube_log.verbose_debug RETURN BINARY_INTEGER;

SELECT dbms_cube_log.verbose_debug

      FROM dual;

VERBOSE_INFO

Log level function dbms_cube_log.verbose_info RETURN BINARY_INTEGER;

SELECT dbms_cube_log.verbose_info

      FROM dual;

VERBOSE_NOTICE

Log level function dbms_cube_log.verbose_notice RETURN BINARY_INTEGER;

SELECT dbms_cube_log.verbose_notice

      FROM dual;

VERBOSE_STATS

Log level function dbms_cube_log.verbose_stats RETURN BINARY_INTEGER;

SELECT dbms_cube_log.verbose_stats

      FROM dual;

VERSION

Retrieve version of table, or current default version if table name is NULL dbms_cube_log.version( log_type IN BINARY_INTEGER DEFAULT dbms_cube_log.type_operations_c, tblname IN VARCHAR2 DEFAULT NULL) RETURN BINARY_INTEGER;

SELECT dbms_cube_log.version FROM dual;

WRITE_TO_OPLOG

Undocumented logging function dbms_cube_log.write_to_oplog( oplogHandleId IN NUMBER DEFAULT NULL, msgName IN VARCHAR2, -- cannot be null msgText IN VARCHAR2 DEFAULT NULL, details IN CLOB DEFAULT NULL, component IN VARCHAR2 DEFAULT 'PLSQL', operation IN VARCHAR2 DEFAULT NULL, -- cannot be null (read your code Oracle) recordLogLevel IN BINARY INTEGER DEFAULT LEVEL_LOW_C);

TBD