Example 6: Filtering Output by Time Range

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.

  1. Create a list of redo log files to mine.

    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');
    
  2. Query the V$LOGMNR_LOGS to see the list of redo log files.

    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
    
  3. Adjust the list of redo log files.

    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);
    
  4. Query the V$LOGMNR_CONTENTS view.

    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');
    
  5. End the LogMiner session.

    EXECUTE DBMS_LOGMNR.END_LOGMNR();