When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files. Oracle recommends that you regularly back up the dictionary extract to ensure correct analysis of older redo log files.
To extract database dictionary information to a flat file, use the
DBMS_LOGMNR_D.BUILD procedure with the
Be sure that no DDL operations occur while the dictionary is being built.
The following steps describe how to extract a dictionary to a flat file. Steps 1 and 2 are preparation steps. You only need to do them once, and then you can extract a dictionary to a flat file as many times as you want to.
DBMS_LOGMNR_D.BUILDprocedure requires access to a directory where it can place the dictionary file. Because PL/SQL procedures do not normally access user directories, you must specify a directory for use by the
DBMS_LOGMNR_D.BUILDprocedure or the procedure will fail. To specify a directory, set the initialization parameter,
UTL_FILE_DIR, in the initialization parameter file.
For example, to set
UTL_FILE_DIR to use
/oracle/database as the directory where the dictionary file is placed, place the following in the initialization parameter file:
UTL_FILE_DIR = /oracle/database
Remember that for the changes to the initialization parameter file to take effect, you must stop and restart the database.
STARTUPcommand mounts and opens the database:
DBMS_LOGMNR_D.BUILD. Specify a file name for the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - '/oracle/database/', - DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
You could also specify a file name and location without specifying the
STORE_IN_FLAT_FILE option. The result would be the same.