Example 2: Grouping DML Statements into Committed Transactions

As shown in the first example, "Example 1: Finding All Modifications in the Last Archived Redo Log File", LogMiner displays all modifications it finds in the redo log files that it analyzes by default, regardless of whether the transaction has been committed or not. In addition, LogMiner shows modifications in the same order in which they were executed. Because DML statements that belong to the same transaction are not grouped together, visual inspection of the output can be difficult. Although you can use SQL to group transactions, LogMiner provides an easier way. In this example, the latest archived redo log file will again be analyzed, but it will return only committed transactions.

  1. Determine which redo log file was most recently archived by the database.

    SELECT NAME FROM V$ARCHIVED_LOG
       WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
    
    NAME                            
    -------------------------------------------
    /usr/oracle/data/db1arch_1_16_482701534.dbf
    
  2. Specify the redo log file that was returned by the query in Step 1. The list will consist of one redo log file.

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
       LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
       OPTIONS => DBMS_LOGMNR.NEW);
    
  3. Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
       LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
       OPTIONS => DBMS_LOGMNR.NEW);
    
  4. Query the V$LOGMNR_CONTENTS view.

    Although transaction 1.11.1476 was started before transaction 1.1.1484 (as revealed in "Example 1: Finding All Modifications in the Last Archived Redo Log File"), it committed after transaction 1.1.1484 committed. In this example, therefore, transaction 1.1.1484 is shown in its entirety before transaction 1.11.1476. The two transactions that did not commit within the redo log file being analyzed are not returned.

    SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO, 
       SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
    ;
    USR    XID          SQL_REDO                        SQL_UNDO
    ----   ---------    ------------------------------- ---------------------------------
         
    OE     1.1.1484   set transaction read write;
    
    OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION" 
                      set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" = 
                      TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                      "PRODUCT_ID" = '1799' and          "PRODUCT_ID" = '1799' and
                      "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                      TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                      ROWID = 'AAAHTKAABAAAY9mAAB';      ROWID = 'AAAHTKAABAAAY9mAAB'; 
                                                                                    
    OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION"
                      set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" =
                      TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                      "PRODUCT_ID" = '1801' and          "PRODUCT_ID" = '1801' and
                      "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                      TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                      ROWID = 'AAAHTKAABAAAY9mAAC';      ROWID ='AAAHTKAABAAAY9mAAC';
    
    OE     1.1.1484   commit;
                                
    HR     1.11.1476  set transaction read write;
    
    HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES" 
                      "EMPLOYEE_ID","FIRST_NAME",       where "EMPLOYEE_ID" = '306'
                      "LAST_NAME","EMAIL",              and "FIRST_NAME" = 'Nandini'
                      "PHONE_NUMBER","HIRE_DATE",       and "LAST_NAME" = 'Shastry'
                      "JOB_ID","SALARY",                and "EMAIL" = 'NSHASTRY'
                      "COMMISSION_PCT","MANAGER_ID",    and "PHONE_NUMBER" = '1234567890'
                      "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-JAN-2012
                      ('306','Nandini','Shastry',       13:34:43', 'dd-mon-yyyy hh24:mi:ss') 
                      'NSHASTRY', '1234567890',         and "JOB_ID" = 'HR_REP' and 
                      TO_DATE('10-jan-2012 13:34:43',   "SALARY" = '120000' and 
                      'dd-mon-yyy hh24:mi:ss'),         "COMMISSION_PCT" = '.05' and
                      'HR_REP','120000', '.05',         "DEPARTMENT_ID" = '10' and
                      '105','10');                      ROWID = 'AAAHSkAABAAAY6rAAO';
    
    HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES"
                      "EMPLOYEE_ID","FIRST_NAME",       "EMPLOYEE_ID" = '307' and 
                      "LAST_NAME","EMAIL",              "FIRST_NAME" = 'John' and
                      "PHONE_NUMBER","HIRE_DATE",       "LAST_NAME" = 'Silver' and
                      "JOB_ID","SALARY",                "EMAIL" = 'JSILVER' and 
                      "COMMISSION_PCT","MANAGER_ID",    "PHONE_NUMBER" = '5551112222'
                      "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-jan-2012
                      ('307','John','Silver',           13:41:03', 'dd-mon-yyyy hh24:mi:ss') 
                       'JSILVER', '5551112222',         and "JOB_ID" ='105' and "DEPARTMENT_ID" 
                      TO_DATE('10-jan-2012 13:41:03',   = '50' and ROWID = 'AAAHSkAABAAAY6rAAP'; 
                      'dd-mon-yyyy hh24:mi:ss'),
                      'SH_CLERK','110000', '.05',
                      '105','50');                
    
    HR     1.11.1476  commit;
    
  5. End the LogMiner session.

    EXECUTE DBMS_LOGMNR.END_LOGMNR();