Example 3: Using Continuous Mining to Include Future Values in a Query

To specify that a query not finish until some future time occurs or SCN is reached, use the CONTINUOUS_MINE option and set either the ENDTIME or ENDSCN option in your call to the DBMS_LOGMNR.START_LOGMNR procedure to a time in the future or to an SCN value that has not yet been reached.

This examples assumes that you want to monitor all changes made to the table hr.employees from now until 5 hours from now, and that you are using the dictionary in the online catalog.

Steps for Using Continuous Mining to Include Future Values in a Query

Step 1: Start LogMiner.

EXECUTE DBMS_LOGMNR.START_LOGMNR(-

   STARTTIME => SYSDATE, -
   ENDTIME   => SYSDATE + 5/24, -
   OPTIONS   => DBMS_LOGMNR.CONTINUOUS_MINE  + -
                DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step 2: Query the V$LOGMNR_CONTENTS view.

This select operation will not complete until it encounters the first redo log file record that is generated after the time range of interest (5 hours from now). You can end the select operation prematurely by pressing Ctrl+C.

This example specifies the SET ARRAYSIZE statement so that rows are displayed as they are entered in the redo log file. If you do not specify the SET ARRAYSIZE statement, then rows are not returned until the SQL internal buffer is full.

SET ARRAYSIZE 1;
SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS
   WHERE  SEG_OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES';

Step 3: End the LogMiner session.

EXECUTE DBMS_LOGMNR.END_LOGMNR();