The logs and their contents are described later in this topic.

• Cube Build Log

• Cube Dimension Compile Log

• Cube Operations Log

• Cube Rejected Records Log

DBMS_CUBE_LOG provides functions that return the binary integer for each log type. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• TYPE_BUILD Function

• TYPE_DIMENSION_COMPILE Function

• TYPE_OPERATIONS Function

• TYPE_REJECTED_RECORDS Function

These are the available targets:

• Disk file

• LOB

• Database table

• Trace file

See ENABLE Procedure for more information about creating multiple targets.

DBMS_CUBE_LOG provides functions that return the binary integer for each target type. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• TARGET_FILE Function

• TARGET_LOB Function

• TARGET_TABLE Function

• TARGET_TRACE Function

• LOWEST: Logs the status of each command used to build the cube dimensions and cubes, the use of slave processes, and summary records. This is the basic logging level.

• LOW: Logs messages from the OLAP engine, such as start and finish records for SQL Import, Aggregate, and Update.

• MEDIUM: Logs messages at the level used by Analytic Workspace Manager.

• HIGH: Logs messages that provide tuning information, such as composite lengths, partitioning details, object sizes, and aggregation work lists. This level is intended for use by Oracle Field Services.

• HIGHEST: Logs debugging messages and other information typically sent to a trace file. This level is intended for use by Oracle Support Services.

DBMS_CUBE_LOG provides functions that return the binary integer for each verbosity level. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• LEVEL_LOWEST Function

• LEVEL_LOW Function

• LEVEL_MEDIUM Function

• LEVEL_HIGH Function

• LEVEL_HIGHEST Function

To create a Cube Build log:

• Execute the TABLE_CREATE procedure.

The following command creates a Cube Build log with the default name of CUBE_BUILD_LOG:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_build);

To create a Cube Dimension Compile log, Cube Operations log, or Cube Rejected Records log with a database table target:

1. Execute the TABLE_CREATE procedure to create the table.

2. Execute the ENABLE procedure to begin logging.

These commands create and enable a Cube Operations table with the default name of CUBE_OPERATIONS_LOG and the default verbosity level:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_operations);
EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations);
 

To create a Cube Dimension Compile log, Cube Operations log, or Cube Rejected Records log with a trace file, disk file, or LOB target:

• Execute the ENABLE procedure.

This command enables the Cube Rejected Records log, sets verbosity to the lowest level, and directs the output to a disk file named rejects.log in the WORK_DIR database directory:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_rejected_records, -
     dbms_cube_log.target_file, dbms_cube_log.level_lowest, -
     'WORK_DIR/rejects.log');

The contents of the Cube Build log are refreshed continuously during a build. You can query the log at any time to evaluate the progress of the build and to estimate the time to completion.

The default name of the Cube Build log is CUBE_BUILD_LOG. The following table describes its contents.

The checks include:

• Circularity: Hierarchies are defined by parent-child relations among dimension members. Circularity occurs when a dimension member is specified as its own ancestor or descendant.

• Hierarchy type: Hierarchies can be level based or value based. You can define a cube so that only level-based hierarchies are valid, such as a cube materialized view.

• Level options: Level-based hierarchies can be regular, ragged, or skip level. You can define a dimension so that only regular hierarchies are valid, such as a Time dimension.

The default name of the Cube Dimension Compile log is CUBE_DIMENSION_COMPILE. The following table describes its contents.

The default name of the Cube Operations log is CUBE_OPERATIONS_LOG. The following table describes its contents.

A single row in the source table may have errors in more than one field. Each field generates an error in the log, resulting in multiple rows with the same rowid4

in the SOURCE_ROW column.

The default name of the Cube Rejected Records log is CUBE_REJECTED_RECORDS. The following table describes its contents.

Syntax

DBMS_CUBE_LOG.DEFAULT_NAME (
           LOG_TYPE       IN   BINARY_INTEGER  DEFAULT TYPE_OPERATIONS)
     RETURN VARCHAR2;

Parameters

Returns

The default table name of the specified log type.

Examples

This example returns the default name of the Cube Operations log:

SELECT dbms_cube_log.default_name FROM dual;
 
DEFAULT_NAME
------------------------------
CUBE_OPERATIONS_LOG

The next example returns the default name of the Cube Rejected Records log:

select dbms_cube_log.default_name(dbms_cube_log.type_rejected_records) -
     "Default Name" from dual;
 
Default Name
-------------------------
CUBE_REJECTED_RECORDS

Syntax

DBMS_CUBE_LOG.DISABLE (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT,
          LOG_TARGET     IN   BINARY_INTEGER  DEFAULT);

Parameters

Example

This command disables the dimension compilation error log table:

EXECUTE dbms_cube_log.disable(dbms_cube_log.type_dimension_compile);

The ENABLE procedure also allows you to direct logging to additional output types and to change the amount of detail in the logs. You can enable a log type to each of the log targets. For example, you can enable the Cube Operations log to the trace file, a table, and a file at different verbosity levels, but you cannot enable the Cube Operations log to two files at the same time.

This procedure also checks the format of the logs and updates them if necessary.

Syntax

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

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

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

Parameters

Examples

The following command enables all cube logs:

EXECUTE dbms_cube_log.enable;

The following PL/SQL procedure sets the log level to LEVEL_LOWEST:

BEGIN
     dbms_cube_log.disable(dbms_cube_log.type_rejected_records);
     dbms_cube_log.enable(dbms_cube_log.type_rejected_records,
          dbms_cube_log.target_table, dbms_cube_log.level_lowest);
END;
/

The buffers are flushed automatically throughout a session, but manually flushing them before viewing the logs assures that you can view all of the messages.

Syntax

DBMS_CUBE_LOG.FLUSH ( );

Example

The following example flushes the buffers for all of the logs:

EXECUTE dbms_cube_log.flush;

Syntax

DBMS_CUBE_LOG.GET_LOG (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT TYPE_OPERATIONS,
          LOG_TARGET     IN   BINARY_INTEGER  DEFAULT TARGET_TABLE,
          LOG_LEVEL      OUT  BINARY_INTEGER,
          LOG_LOCATION   OUT  VARCHAR2 );

Parameters

Usage Notes

If log_type is not active, then log_level and log_location are null. Use DBMS_CUBE_LOG.ENABLE to activate a log.

Examples

This PL/SQL procedure provides information about the Cube Rejected Records log:

SET serverout ON format wrapped
 
DECLARE 
     myloglevel  binary_integer;
     mylogtarget varchar2(128);
 
BEGIN
     dbms_cube_log.get_log(dbms_cube_log.type_rejected_records,
     dbms_cube_log.target_table, myloglevel, mylogtarget);

     dbms_output.put_line('Log Level: ' || myloglevel);
     dbms_output.put_line('Log Target: ' || mylogtarget);
END;
/

The procedure generates results like the following:

Log Level: 5

Log Target: GLOBAL.CUBE_REJECTED_RECORDS

Syntax

DBMS_CUBE_LOG.GET_LOG_SPEC ( )
     RETURN VARCHAR2;

Returns

The type and target of all active logs.

Usage Notes

You can use the output from this function as the input to SET_LOG_SPEC.

Examples

The following example shows that the Cube Operations log, Cube Rejected Records log, and Cube Dimension Compile log are active. The Cube Operations log is stored in the session trace file and the other logs are stored in tables.

SELECT dbms_cube_log.get_log_spec FROM dual;
 
GET_LOG_SPEC
--------------------------------------------------------------------------------
OPERATIONS(TABLE, TRACE) REJECTED_RECORDS(TABLE[DEBUG])

Syntax

DBMS_CUBE_LOG.GET_PARAMETER (
          LOG_TYPE       IN   BINARY_INTEGER,
          LOG_PARAMETER  IN   BINARY_INTEGER )
     RETURN BINARY_INTEGER;

Parameters

Returns

The value of the specified log_parameter.

Examples

This example shows the current maximum number of errors in the Cube Rejected Records log before logging stops. This parameter was previously set with the SET_PARAMETER procedure.

SELECT dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 1) -
     "Maximum Records" FROM dual;
 
Maximum Records
---------------
            100

Syntax

DBMS_CUBE_LOG.LEVEL_HIGH ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to high:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_high);

Syntax

DBMS_CUBE_LOG.LEVEL_HIGHEST ()
     RETURN BINARY_INTEGER;

Returns

5

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to highest:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_highest);

Syntax

DBMS_CUBE_LOG.LEVEL_LOW ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to low:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_low);

Syntax

DBMS_CUBE_LOG.LEVEL_LOWEST ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to lowest:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_lowest);

Syntax

DBMS_CUBE_LOG.LEVEL_MEDIUM ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to medium:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_medium);

Syntax

DBMS_CUBE_LOG.SET_LOG_SPEC (
          LOG_SPEC       IN   VARCHAR2 );

Parameters

Usage Notes

The GET_LOG_SPEC function returns a properly formatted string for SET_LOG_SPEC.

Examples

This PL/SQL procedure disables all logs, verifies that they are disabled, then activates the Cube Operations log and the Cube Rejected Records log.

BEGIN
     dbms_cube_log.disable;
     dbms_output.put_line('Cube Logs: ' || dbms_cube_log.get_log_spec);

     dbms_cube_log.set_log_spec('OPERATIONS(TRACE) REJECTED_RECORDS(TABLE)');
     dbms_output.put_line('Cube Logs: ' || dbms_cube_log.get_log_spec);
END;
/

The output from the procedure verifies that the DISABLE function de-activated all logs, and the SET_LOG_SPEC function activated two logs:

Cube Logs:

Cube Logs: OPERATIONS(TRACE) REJECTED_RECORDS(TABLE)

To obtain the current value of these options, use the GET_PARAMETER function.

Syntax

DBMS_CUBE_LOG.SET_PARAMETER (
          LOG_TYPE       IN   BINARY_INTEGER,
          LOG_PARAMETER  IN   BINARY_INTEGER,
          VALUE          IN   BINARY_INTEGER );

Parameters

Examples

This PL/SQL procedure sets the two parameters, then uses the GET_PARAMETER function to show the settings:

BEGIN
     dbms_cube_log.set_parameter(dbms_cube_log.type_rejected_records, 1, 150);
     dbms_cube_log.set_parameter(dbms_cube_log.type_rejected_records, 2, 5);

     dbms_output.put_line('Max rejected records: ' ||
        dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 1));

     dbms_output.put_line('Buffer time: ' ||
        dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 2) || 
          ' seconds');
END;
/

The procedure displays this information:

Max rejected records: 150

Buffer time: 5 seconds

TABLE_CREATE also upgrades existing log tables to the current version while preserving the data.

Syntax

DBMS_CUBE_LOG.TABLE_CREATE (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT,
          TBLNAME        IN   VARCHAR2        DEFAULT );

Parameters

Examples

This example creates a Cube Operations log table named CUBE_OPERATIONS_LOG:

EXECUTE dbms_cube_log.table_create;

This example creates a Cube Rejected Records log table named CUBE_REJECTED_RECORDS:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_rejected_records);

The next example creates a Cube Build log table named MY_BUILD_LOG:

EXECUTE dbms_cube_log.table_create -
     (dbms_cube_log.type_build, 'MY_BUILD_LOG');

Syntax

DBMS_CUBE_LOG.TARGET_FILE ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log file:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_file);

Syntax

DBMS_CUBE_LOG.TARGET_LOB ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log LOB:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_lob);

Syntax

DBMS_CUBE_LOG.TARGET_TABLE ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TARGET parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log table:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_table);

Syntax

DBMS_CUBE_LOG.TARGET_TRACE ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TARGET parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log trace file:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_trace);

Syntax

DBMS_CUBE_LOG.TYPE_BUILD ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Build log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_build) "Log Name" -
     FROM dual;
 
Log Name
-------------------------
CUBE_BUILD_LOG

Syntax

DBMS_CUBE_LOG.TYPE_DIMENSION_COMPILE ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Dimension Compile log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_dimension_compile) -
     "Log Name" FROM dual;
 
Log Name
-------------------------
CUBE_DIMENSION_COMPILE

Syntax

DBMS_CUBE_LOG.TYPE_OPERATIONS ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Dimension Compile log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_operations) "Log Name" -
     FROM dual;
 
Log Name
-------------------------
CUBE_OPERATIONS_LOG

Syntax

DBMS_CUBE_LOG.TYPE_REJECTED_RECORDS ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Rejected Records log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_rejected_records) -
     "Log Name" FROM dual;
 
Log Name
-------------------------
CUBE_REJECTED_RECORDS

Syntax

DBMS_CUBE_LOG.VERSION (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT 1,
          TBLNAME        IN   VARCHAR2        DEFAULT NULL)
     RETURN BINARY_INTEGER;

Parameters

Returns

A version number

Examples

This example returns the current version of the Cube Operations log:

SELECT dbms_cube_log.version FROM dual;
 
   VERSION
----------
         2

This example returns the version number of an existing Cube Rejected Records log named CUBE_REJECTED_RECORDS.

SELECT dbms_cube_log.version(dbms_cube_log.type_rejected_records, -
     'CUBE_REJECTED_RECORDS') version FROM dual;
 
   VERSION
----------
         2