Example 3: Formatting the Reconstructed SQL

As shown in "Example 2: Grouping DML Statements into Committed Transactions ", using the COMMITTED_DATA_ONLY option with the dictionary in the online redo log file is an easy way to focus on committed transactions. However, one aspect remains that makes visual inspection difficult: the association between the column names and their respective values in an INSERT statement are not apparent. This can be addressed by specifying the PRINT_PRETTY_SQL option. Note that specifying this option will make some of the reconstructed SQL statements nonexecutable.

  1. Determine which redo log file was most recently archived.

    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.

    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 and PRINT_PRETTY_SQL options.

    EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                  DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                  DBMS_LOGMNR.PRINT_PRETTY_SQL);
    

    The DBMS_LOGMNR.PRINT_PRETTY_SQL option changes only the format of the reconstructed SQL, and therefore is useful for generating reports for visual inspection.

  4. Query the V$LOGMNR_CONTENTS view for SQL_REDO statements.

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

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

    EXECUTE DBMS_LOGMNR.END_LOGMNR();