Summary of DBMS_LOGMNR_D Subprograms

Table 28-1 describes the procedures in the DBMS_LOGMNR_D supplied package.

Table 28-1  DBMS_LOGMNR_D Package Subprograms

Subprogram	Description
BUILD Procedure	Extracts the database dictionary to either a flat file or a file in the redo logs.
SET_TABLESPACE Procedure	Re-creates all LogMiner tables in an alternate tablespace.

BUILD Procedure

The syntax for the DBMS_LOGMNR_D.BUILD procedure is as follows:

Syntax

DBMS_LOGMNR_D.BUILD (
dictionary_filename IN VARCHAR2,
dictionary_location IN VARCHAR2,
options IN NUMBER);

Parameters

Table 28-2 describes the parameters for the BUILD procedure.

Table 28-2  BUILD Procedure Parameters

Parameter	Description
dictionary_filename	Name of the dictionary file
dictionary_location	Path to file directory
options	Specifies that the dictionary is written to either a flat file (STORE_IN_FLAT_FILE) or the redo logs (STORE_IN_REDO_LOGS) destination

To extract the dictionary to a flat file, you must supply a filename and location.

To extract the dictionary to the redo logs, specify only the STORE_IN_REDO_LOGS option. The size of the dictionary may cause it to be contained in multiple redo logs.

In summary, the combinations of parameters used result in the following behavior:

• If you do not specify any parameters, an error message is returned.
• If you specify a filename and location, without any options, the dictionary is extracted to a flat file with that name.
• If you specify a filename and location, as well as the DBMS_LOGMNR_D.STORE_IN_FLAT_FILE option, the dictionary is extracted to a flat file with the specified name.
• If you do not specify a filename and location, but do specify the DBMS_LOGMNR_D.STORE_IN_REDO_LOGS option, the dictionary is extracted to the redo logs.
• If you specify a filename and location, as well as the STORE_IN_REDO_LOGS option, an error is returned.

Exceptions

• ORA-1308: initialization parameter UTL_FILE_DIR is not set.
• ORA-1336 - this error is returned under the following conditions:
  1. Dictionary_location does not exist.
  2. UTL_FILE_DIR is not set to have access to dictionary_location.
  3. Dictionary file is read only.

Usage Notes

• Ideally, the dictionary file will be created after all dictionary changes to a database and prior to the creation of any redo logs that are to be analyzed. As of Oracle9i release 1 (9.0.1), you can use LogMiner to dump the dictionary to the redo logs, perform DDL operations, and dynamically apply the changes to the LogMiner dictionary.
• The DBMS_LOGMNR_D.BUILD procedure will not run if there are any ongoing DDL operations.
• To use the DBMS_LOGMNR_D.BUILD procedure, the database whose files you want to analyze must be mounted and open.

• To monitor progress of the dictionary build, issue the SET SERVEROUTPUT ON command.
• When extracting a dictionary to a flat file, the procedure queries the dictionary tables of the current database and creates a text-based file containing the contents of the tables. To extract a dictionary to a flat file, the following conditions must be met:
  • The dictionary file must be created from the same database that generated the redo logs you want to analyze
  • You must specify a directory for use by the PL/SQL procedure. To do so, set the initialization parameter UTL_FILE_DIR in the init.ora file. For example:

    UTL_FILE_DIR = /oracle/dictionary
    

    If you do not set this parameter, the procedure will fail.

  • You must ensure that no DDL operations occur while the dictionary build is running. Otherwise, the dictionary file may not contain a consistent snapshot of the data dictionary.
• To extract a dictionary file to the redo logs, the following conditions must be met:
  • Supplemental logging (at least the minimum level) must be enabled to ensure that the redo logs contain useful information. See Oracle9i Database Administrator's Guide for information about using supplemental logging with LogMiner.
  • The DBMS_LOGMNR_D.BUILD procedure must be run on a system that is running Oracle9i or later
  • Archiving mode must be enabled in order to generate usable redo
  • Oracle9i compatibility must be employed
  • The mining system must be Oracle9i or later
  • The dictionary redo files must be created from the same database that generated the redo logs you want to analyze

Example 1: Extracting the Dictionary to a Flat File

The following example extracts the dictionary file to a flat file named dictionary.ora in a specified path (/oracle/database).

SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora', - 
  2 '/oracle/database/', -
  3 options => dbms_logmnr_d.store_in_flat_file);

Example 2: Extracting the Dictionary to the Redo Logs

The following example extracts the dictionary to the redo logs.

SQL> EXECUTE dbms_logmnr_d.build ( -
  2 options => dbms_logmnr_d.store_in_redo_logs);

SET_TABLESPACE Procedure

By default all LogMiner tables are created to use the SYSTEM tablespace. However, it may be desirable to alter LogMiner tables to employ an alternate tablespace. Use this routine to re-create all LogMiner tables in an alternate tablespace.

Parameters

Table 28-3 describes the parameters for the SET_TABLESPACE procedure.

Table 28-3  SET_TABLESPACE Parameters

Parameter	Description
new_tablespace	A string naming a preexistent tablespace. To re-create all LogMiner tables to employ this tablespace, supply only this parameter.
dictionary_tablespace	A string naming a preexistent tablespace. This parameter places LogMiner Dictionary data in a tablespace different from that where LogMiner spill data is to be written. This parameter overrides the new_tablespace parameter with respect to LogMiner Dictionary tables.
spill_tablespace	A string naming a preexistent tablespace. This parameter places LogMiner spill data in a tablespace different from that where LogMiner Dictionary data is to be written. This parameter overrides the new_tablespace parameter with respect to LogMiner spill tables.

Usage Notes

• There can be no LogMiner sessions running at the time this procedure is run, nor can LogMiner have been terminated abnormally prior to this procedure being run. Either situation can cause unpredictable results.
• Though the intent is that this routine is to be run only once to configure LogMiner for use by other products, it can be run multiple times should it be necessary to redefine the tablespaces that are to be employed. However, the previous usage note is still enforced. Because the techniques required to force layered products to terminate their LogMiner sessions may be non-trivial, Oracle Corporation does not recommend that this routine be used more than once.
• Certain layered products require that this routine be used to alter the tablespace of all LogMiner tables before the layered product will operate.
• Certain performance optimizations can be made when LogMiner tables do not employ the SYSTEM tablespace. Specifically, certain easily repeatable operations, such as memory spill, LogMiner dictionary load, and index creation will not be logged. This would have unacceptable implications with respect to the SYSTEM tablespace in the event of a database recovery.
• Users of this routine must supply an existing tablespace. Information about tablespaces and how to create them is available in Oracle9i Database Concepts and Oracle9i SQL Reference.

Example: Using the DBMS_LOGMNR_D.SET_TABLESPACE Procedure

The following example shows creation of an alternate tablespace and execution of the DBMS_LOGMNR_D.SET_TABLESPACE procedure.

SQL> CREATE TABLESPACE  logmnrts$ datafile '/usr/oracle/dbs/logmnrts'
  2 SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL> EXECUTE dbms_logmnr_d.set_tablespace('logmnrts$');