How to read the contents of Oracle Archive files (Log miner)

1. Determine which archive files you will read (e.x we will read archives between 395552..395557)


2. execute following command with user sys (Note that first add command called with sys.dbms_logmnr.new parameter and others callled with dbms_logmnr.addfile )
   
   begin
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395552_1_571790194.arc',options=>sys.dbms_logmnr.new);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395553_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395554_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395555_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395556_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395557_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
   
   end;
   /


3. You can select log content from V$LOGMNR_CONTENTS;
   select * from V$LOGMNR_CONTENTS;


4. Selecting from V$LOGMNR_CONTENTS is very slow and another restriction is You can select the content of V$LOGMNR_CONTENTS only from the session that you execute the dbms_logmnr commands.If you want to to query it multiple times suggest you to insert this data to another local table
   
   create table logminer_table tablespace tools as select * from V$LOGMNR_CONTENTS;
   
   also you can create index on it in order to query time based
   create index logminer_table_ix on logminer_table("TIMESTAMP") tablespace tools ;
   
   Note:Be carefull for the space of logminer_table .Each archive will cover 4-5 bigger than the size of the archive .(for the above example think each archive is about 100m than for 6 archive. logminer_table will cover nealy 6*5*100m =3g )


5. After you finish with log miner, execute following query:
   begin
   sys.dbms_logmnr.end_logmnr;
   end;


6. Then you can select it from table logminer_table