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.
This example assumes that you know you want to mine the redo log file that 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
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);
Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.
EXECUTE DBMS_LOGMNR.START_LOGMNR( - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - DBMS_LOGMNR.COMMITTED_DATA_ONLY);
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;
EXECUTE DBMS_LOGMNR.END_LOGMNR();