Log Miner

These are steps to use log miner:

1-) Execute following PL/SQL block as sys user (note that first dbms_logmnr.add_logfile procedure is called with dbms_logmnr.new argument while others are called with dbms_logmnr.addfile argument):

begin
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.addfile);
...
sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;

2-) You can select log content using following query:

select * from V$LOGMNR_CONTENTS;

3-) Because content is read from file sequencely, it is very slow. If you are planning to query it multiple times, i strongly suggest you to insert log miner data to a local table and create an index on timestamp column. Requested storage for local table and its index is usually at around 4*(log size)*(number of log files).

create table LOGMNR TABLESPACE TOOLS as select * from V$LOGMNR_CONTENTS;
CREATE INDEX LOGMNR_IX1 ON LOGMNR ("TIMESTAMP") TABLESPACE TOOLS ;

4-) After you finish with log miner, execute following query:

begin
sys.dbms_logmnr.end_logmnr;
end;