dbms_logmnr package

Note -

1.You must have the EXECUTE_CATALOG_ROLE role to use the DBMS_LOGMNR package.

2.After you have created a dictionary file with DBMS_LOGMNR_D, you can begin analyzing archived redo logs.

The DBMS_LOGMNR_D package contains the LogMiner procedures, DBMS_LOGMNR_D.BUILD and DBMS_LOGMNR_D.SET_TABLESPACE.The DBMS_LOGMNR_D.BUILD procedure extracts the dictionary to either the redo logs or to a flat file. This information is saved in preparation for future analysis of redo logs using the LogMiner tool. The DBMS_LOGMNR_D.SET_TABLESPACE procedure re-creates all LogMiner tables in an alternate tablespace.

3.The DBMS_LOGMNR_D.BUILD procedure will not run if there are any ongoing DDL operations.

4.1When 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:

1.The dictionary file must be created from the same database that generated the redo logs you want to analyze

2.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

4.2To extract a dictionary file to the redo logs, the following conditions must be met:

1.The DBMS_LOGMNR_D.BUILD procedure must be run on a system that is running Oracle9i or later

2.Archiving mode must be enabled in order to generate usable redo

3.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

SQL> alter database add supplemental log data;
Database altered.

SQL> execute dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
PL/SQL procedure successfully completed.

yet to be complete.