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
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.
STARTTIME => SYSDATE, - ENDTIME => SYSDATE + 5/24, - OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE + - DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
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
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';
End the LogMiner session.