In the previous two examples, rows were filtered by specifying a timestamp-based predicate (timestamp > '10-jan-2012 15:59:53') in the query. However, a more efficient way to filter out redo records based on timestamp values is by specifying the time range in the DBMS_LOGMNR.START_LOGMNR procedure call, as shown in this example.
Suppose you want to mine redo log files generated since a given time. The following procedure creates a list of redo log files based on a specified time. The subsequent SQL EXECUTE statement calls the procedure and specifies the starting time as 2 p.m. on Jan-13-2012.
--
-- my_add_logfiles
-- Add all archived logs generated after a specified start_time.
--
CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time IN DATE) AS
CURSOR c_log IS
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= in_start_time;
count pls_integer := 0;
my_option pls_integer := DBMS_LOGMNR.NEW;
BEGIN
FOR c_log_rec IN c_log
LOOP
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name,
OPTIONS => my_option);
my_option := DBMS_LOGMNR.ADDFILE;
DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);
END LOOP;
END;
/
EXECUTE my_add_logfiles(in_start_time => '13-jan-2012 14:00:00');
This example includes the size of the redo log files in the output.
SELECT FILENAME name, LOW_TIME start_time, FILESIZE bytes
FROM V$LOGMNR_LOGS;
NAME START_TIME BYTES
----------------------------------- -------------------- ----------------
/usr/orcl/arch1_310_482932022.dbf 13-jan-2012 14:02:35 23683584
/usr/orcl/arch1_311_482932022.dbf 13-jan-2012 14:56:35 2564096
/usr/orcl/arch1_312_482932022.dbf 13-jan-2012 15:10:43 23683584
/usr/orcl/arch1_313_482932022.dbf 13-jan-2012 15:17:52 23683584
/usr/orcl/arch1_314_482932022.dbf 13-jan-2012 15:23:10 23683584
/usr/orcl/arch1_315_482932022.dbf 13-jan-2012 15:43:22 23683584
/usr/orcl/arch1_316_482932022.dbf 13-jan-2012 16:03:10 23683584
/usr/orcl/arch1_317_482932022.dbf 13-jan-2012 16:33:43 23683584
/usr/orcl/arch1_318_482932022.dbf 13-jan-2012 17:23:10 23683584
Suppose you realize that you want to mine just the redo log files generated between 3 p.m. and 4 p.m.
You could use the query predicate (timestamp > '13-jan-2012 15:00:00' and timestamp < '13-jan-2012 16:00:00') to accomplish this. However, the query predicate is evaluated on each row returned by LogMiner, and the internal mining engine does not filter rows based on the query predicate. Thus, although you only wanted to get rows out of redo log files arch1_311_482932022.dbf to arch1_315_482932022.dbf, your query would result in mining all redo log files registered to the LogMiner session.
Furthermore, although you could use the query predicate and manually remove the redo log files that do not fall inside the time range of interest, the simplest solution is to specify the time range of interest in the DBMS_LOGMNR.START_LOGMNR procedure call.
Although this does not change the list of redo log files, LogMiner will mine only those redo log files that fall in the time range specified.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTTIME => '13-jan-2012 15:00:00', -
ENDTIME => '13-jan-2012 16:00:00', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
SELECT TIMESTAMP, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'OE';
TIMESTAMP XID SQL_REDO
--------------------- ----------- --------------------------------
13-jan-2012 15:29:31 1.17.2376 update "OE"."PRODUCT_INFORMATION"
set
"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
where
"PRODUCT_ID" = 3399 and
"WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
ROWID = 'AAAHTKAABAAAY9TAAE';
13-jan-2012 15:29:34 1.17.2376 insert into "OE"."PRODUCT_TRACKING"
values
"PRODUCT_ID" = 3399,
"MODIFIED_TIME" = TO_DATE('13-jan-2012 15:29:34',
'dd-mon-yyyy hh24:mi:ss'),
"OLD_LIST_PRICE" = 815,
"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');
13-jan-2012 15:52:43 1.15.1756 update "OE"."PRODUCT_INFORMATION"
set
"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
where
"PRODUCT_ID" = 1768 and
"WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
ROWID = 'AAAHTKAABAAAY9UAAB';
13-jan-2012 15:52:43 1.15.1756 insert into "OE"."PRODUCT_TRACKING"
values
"PRODUCT_ID" = 1768,
"MODIFIED_TIME" = TO_DATE('13-jan-2012 16:52:43',
'dd-mon-yyyy hh24:mi:ss'),
"OLD_LIST_PRICE" = 715,
"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');
EXECUTE DBMS_LOGMNR.END_LOGMNR();