extracting SQLS from redo logs 10g to tracks all/commited changes

I -to track all(commited+non commited changes)

since v$logmmr_contents data is only persistent to session in which log miner is run a table logmined_commited is created to store all extracted information.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

In below options parameters is saving the time of adding individual redo logs/archive logs.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-jan-2008 01:00:00', -
> ENDTIME => '03-jan-2008 21:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
98558

SQL> create table extlogs as select * from v$logmnr_contents;

Table created.
------

OR

II -to track commited changes only

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-JAN-2008 01:00:00', -
> ENDTIME => '03-JAN-2008 23:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.


SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
54994


SQL> create table extlogs as select * from v$logmnr_contents ;

Table created.

SQL> exec DBMS_LOGMNR.END_LOGMNR( );

PL/SQL procedure successfully completed.

sample query from extracted logs:
SELECT SEG_OWNER, SEG_NAME,operation, COUNT(*) AS Hits FROM
sys.logmined_commited
WHERE SEG_OWNER NOT IN('SYS','SYSTEM','WKSYS')
AND TRUNC(timestamp)='01-jan-07'
GROUP BY SEG_OWNER, SEG_NAME,operation
ORDER BY 1,2,3,4

above shows tables which had undergone DMLS

below shows DML type,corresponding DML statement and equivalent UNDO statement to undo DML for above affected objects.

SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM sys.logmined_commited
WHERE SEG_OWNER = 'SEGOWNER'
AND SEG_NAME = 'segment_name'
AND OPERATION = 'UPDATE'
AND USERNAME = 'username';