Calling DBMS_LOGMNR.START_LOGMNR Multiple Times

Even after you have successfully called DBMS_LOGMNR.START_LOGMNR and selected from the V$LOGMNR_CONTENTS view, you can call DBMS_LOGMNR.START_LOGMNR again without ending the current LogMiner session and specify different options and time or SCN ranges. The following list presents reasons why you might want to do this:

  • You want to limit the amount of redo data that LogMiner has to analyze.

  • You want to specify different options. For example, you might decide to specify the PRINT_PRETTY_SQL option or that you only want to see committed transactions (so you specify the COMMITTED_DATA_ONLY option).

  • You want to change the time or SCN range to be analyzed.

Example Situations Where it Could Be Useful to Call DBMS_LOGMNR.START_LOGMNR Multiple Times
Example 1: Mining Only a Subset of the Data in the Redo Log Files

Suppose the list of redo log files that LogMiner has to mine include those generated for an entire week. However, you want to analyze only what happened from 12:00 to 1:00 each day. You could do this most efficiently by:

  1. Calling DBMS_LOGMNR.START_LOGMNR with this time range for Monday.

  2. Selecting changes from the V$LOGMNR_CONTENTS view.

  3. Repeating Steps 1 and 2 for each day of the week.

If the total amount of redo data is large for the week, then this method would make the whole analysis much faster, because only a small subset of each redo log file in the list would be read by LogMiner.

Example 2: Adjusting the Time Range or SCN Range

Suppose you specify a redo log file list and specify a time (or SCN) range when you start LogMiner. When you query the V$LOGMNR_CONTENTS view, you find that only part of the data of interest is included in the time range you specified. You can call DBMS_LOGMNR.START_LOGMNR again to expand the time range by an hour (or adjust the SCN range).

Example 3: Analyzing Redo Log Files As They Arrive at a Remote Database

Suppose you have written an application to analyze changes or to replicate changes from one database to another database. The source database sends its redo log files to the mining database and drops them into an operating system directory. Your application:

  1. Adds all redo log files currently in the directory to the redo log file list

  2. Calls DBMS_LOGMNR.START_LOGMNR with appropriate settings and selects from the V$LOGMNR_CONTENTS view

  3. Adds additional redo log files that have newly arrived in the directory

  4. Repeats Steps 2 and 3, indefinitely