Determine the archive log path:
SQL> archive log list;
If "Archive destination" is "DB_RECOVERY_FILE_DEST":
SQL> show parameter DB_RECOVERY_FILE_DEST;
Select the archive log file to use for logminer
such as "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC"
e.g.
delete from employees where employee_id = 206
Run following codes as SYS user:
BEGIN
DBMS_LOGMNR.add_logfile (
logfilename => 'C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC', options => DBMS_LOGMNR.new);
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
/
Run these codes for examining the logs in example table (as SYS):
SQL>
CREATE TABLE HR.ex934210760 AS SELECT * FROM v$logmnr_contents;
SQL>
GRANT SELECT ON HR.ex934210760 TO PUBLIC;
To find DML (delete) SQL in example table:
SQL>
select * from HR.ex934210760 where table_name='EMPLOYEES'
SQL> archive log list;
If "Archive destination" is "DB_RECOVERY_FILE_DEST":
SQL> show parameter DB_RECOVERY_FILE_DEST;
Select the archive log file to use for logminer
such as "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC"
e.g.
delete from employees where employee_id = 206
Run following codes as SYS user:
BEGIN
DBMS_LOGMNR.add_logfile (
logfilename => 'C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC', options => DBMS_LOGMNR.new);
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
/
Run these codes for examining the logs in example table (as SYS):
SQL>
CREATE TABLE HR.ex934210760 AS SELECT * FROM v$logmnr_contents;
SQL>
GRANT SELECT ON HR.ex934210760 TO PUBLIC;
To find DML (delete) SQL in example table:
SQL>
select * from HR.ex934210760 where table_name='EMPLOYEES'