Using Logminer Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples.

If your database is in Archivelog Mode and you have already enabled Supplemental Logging before the rows were create(This is done using ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;), You can use LogMiner to find the changes in the database for any user. My test example here finds changes made by TEST user from today. You can change the username to any user and add more columns from V$LOGMNR_CONTENTS view.
SQL> connect /as sysdba
Connected.
SQL>

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > TRUNC(SYSDATE);

NAME
---------------------------------------------------------------------------------------
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC

10 rows selected.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> set lines 200
SQL> set pages 9999
SQL> col USERNAME format a10
SQL> col XID format a10
SQL> col SQL_REDO format a40
SQL> col SQL_UNDO format a40
SQL>
SQL> SELECT username,session#,
to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp,
(XIDUSN '.' XIDSLT '.' XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS WHERE username = 'TEST'
2
SQL>

--- Your results Display here

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.
SQL>