Logminer Setup

Perform Initial Setup Activities

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> create tablespace logmnrts datafile '/u05/oradata/db_name/logmnrts.dbf' size 100M autoextend on maxsize 2048M;

Tablespace created.

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');

PL/SQL procedure successfully completed.

Add Redo Logfiles

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u11/archive/uspibe2/0001S0000008035.ARC',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u11/archive/uspibe2/0001S0000008036.ARC',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

Start a LogMiner Session

To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner, as follows:

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

PL/SQL procedure successfully completed.

Query V$LOGMNR_CONTENTS

desc V$LOGMNR_CONTENTS

SELECT SQL_REDO, TIMESTAMP
FROM V$LOGMNR_CONTENTS
order by TIMESTAMP;

SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME NOT LIKE '%$'
GROUP BYSEG_OWNER, SEG_NAME;

End a LogMiner Session

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.