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
Extracts the database dictionary to either a flat file or a file in the redo logs.
Re-creates all LogMiner tables in an alternate tablespace.
The syntax for the
BUILD procedure is as follows:
dictionary_filename IN VARCHAR2,
dictionary_location IN VARCHAR2,
options IN NUMBER);
Table 28-2 describes the parameters for the
Table 28-2 BUILD Procedure Parameters
Name of the dictionary file
Path to file directory
Specifies that the dictionary is written to either a flat file (
STORE_IN_FLAT_FILE) or the redo logs (
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.
- ORA-1308: initialization parameter
UTL_FILE_DIR is not set.
- ORA-1336 - this error is returned under the following conditions:
- Dictionary_location does not exist.
UTL_FILE_DIR is not set to have access to dictionary_location.
- Dictionary file is read only.
- 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.
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
- 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:
- 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.
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 (
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);
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.
Table 28-3 describes the parameters for the
Table 28-3 SET_TABLESPACE Parameters
A string naming a preexistent tablespace. To re-create all LogMiner tables to employ this tablespace, supply only this parameter.
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.
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.
- 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
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$');