Oracle DBMS_CUBE_LOG
Version 11.2.0.3
 
General Information
Description Provides interfaces which control logging in the OLAP infrastructure
Source {ORACLE_HOME}/rdbms/admin/dbmscbl.sql
First Available 11.2
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_V11200
Dimension Compile
DIMENSION_COMPILE_V112ALPHA BINARY_INTEGER 1
DIMENSION_COMPILE_V112 BINARY_INTEGER 2
DIMENSION_COMPILE_VCURRENT BINARY_INTEGER DIMENSION_COMPILE_V112
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_V112
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_ASSERT DBMS_AW_LIB DBMS_UTILITY
ALL_TAB_COLUMNS DBMS_AW DBMS_OUTPUT  
Exceptions
Exception Name Error Code
INVALID_TYPE ORA-37561
INVALID_TARGET ORA-37562
INVALID_LEVEL ORA-37563
INVALID_VERSION ORA-37564
INVALID_LOCATION ORA-37566
INVALID_SQL_ID ORA-37571
INVALID_ID ORA-37572
NO_LIMITS ORA-37573
INVALID_LOG_MSG_NAME ORA-37577
Security Model Execute is granted to PUBLIC
Subprograms
COMPLETE_OPLOG LEVEL_HIGHEST TYPE_DIMENSION_COMPILE
CREATE_REJECT_SQL LEVEL_LOW TYPE_OPERATIONS
DEFAULT_NAME LEVEL_LOWEST TYPE_REJECTED_RECORDS
DISABLE LEVEL_MEDIUM VERBOSE_ACTION
ENABLE SET_QUERY_ENV VERBOSE_DEBUG
FLUSH START_OPLOG VERBOSE_INFO
GET_LOG TABLE_CREATE VERBOSE_NOTICE
GET_LOG_SPEC TARGET_FILE VERBOSE_STATS
GET_PARAMETER TARGET_LOB VERSION
SET_LOG_SPEC TARGET_TABLE WRITE_TO_OPLOG
SET_PARAMETER TARGET_TRACE  
LEVEL_HIGH TYPE_BUILD  
 
COMPLETE_OPLOG (new 11.2.0.2)
Undocumented logging function dbms_cube_log.complete_oplog(oplogHandleId IN NUMBER);
TBD
 
CREATE_REJECT_SQL (new 11.2.0.3)
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

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

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

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 (new in 11.2.0.2)
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;
 
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 tblname 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 (new in 11.2.0.2)
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
 
Related Topics
DBMS_CUBE
 
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-2013 Daniel A. Morgan All Rights Reserved