--开始实验
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 26 09:33:33 2014
Copyright (c) 1982, 2005, Oracle.? All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set line 130
SQL> select * from v$log;
? ? GROUP#? ? THREAD#? SEQUENCE#? ? ? BYTES? ? MEMBERS ARC STATUS? ? ? ? ? FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
? ? ? ? 1? ? ? ? ? 1? ? ? ? ? 8? 52428800? ? ? ? ? 1 YES INACTIVE? ? ? ? ? ? ? 1402743 2014-12-24
? ? ? ? 2? ? ? ? ? 1? ? ? ? ? 9? 52428800? ? ? ? ? 1 NO? CURRENT? ? ? ? ? ? ? ? 1402823 2014-12-24
? ? ? ? 3? ? ? ? ? 1? ? ? ? ? 7? 52428800? ? ? ? ? 1 YES INACTIVE? ? ? ? ? ? ? 1401824 2014-12-24
SQL> col member for a45
SQL> select group#,member from v$logfile;
? ? GROUP# MEMBER
---------- ---------------------------------------------
? ? ? ? 3 /u01/app/oracle/oradata/ora10g/redo03.log
? ? ? ? 2 /u01/app/oracle/oradata/ora10g/redo02.log
? ? ? ? 1 /u01/app/oracle/oradata/ora10g/redo01.log
--启用日志挖掘
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora10g/redo02.log');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
来看一下LOGMNR工具用到的相关视图:
?
SQL> set pages 100
SQL> col comments for a40
SQL> select * from dict t where t.table_name like '%LOGMNR%';
TABLE_NAME? ? ? ? ? ? ? ? ? ? COMMENTS
------------------------------ ----------------------------------------
DBA_LOGMNR_LOG
DBA_LOGMNR_SESSION
DBA_LOGMNR_PURGED_LOG
V$LOGMNR_CONTENTS? ? ? ? ? ? ? Synonym for V_$LOGMNR_CONTENTS
V$LOGMNR_PARAMETERS? ? ? ? ? ? Synonym for V_$LOGMNR_PARAMETERS
V$LOGMNR_DICTIONARY? ? ? ? ? ? Synonym for V_$LOGMNR_DICTIONARY
V$LOGMNR_LOGS? ? ? ? ? ? ? ? ? Synonym for V_$LOGMNR_LOGS
V$LOGMNR_STATS? ? ? ? ? ? ? ? Synonym for V_$LOGMNR_STATS
V$LOGMNR_DICTIONARY_LOAD? ? ? Synonym for V_$LOGMNR_DICTIONARY_LOAD
GV$LOGMNR_CONTENTS? ? ? ? ? ? Synonym for GV_$LOGMNR_CONTENTS
GV$LOGMNR_PARAMETERS? ? ? ? ? Synonym for GV_$LOGMNR_PARAMETERS
GV$LOGMNR_DICTIONARY? ? ? ? ? Synonym for GV_$LOGMNR_DICTIONARY
GV$LOGMNR_LOGS? ? ? ? ? ? ? ? Synonym for GV_$LOGMNR_LOGS
V$LOGMNR_LOGFILE? ? ? ? ? ? ? Synonym for V_$LOGMNR_LOGFILE
V$LOGMNR_PROCESS? ? ? ? ? ? ? Synonym for V_$LOGMNR_PROCESS
V$LOGMNR_LATCH? ? ? ? ? ? ? ? Synonym for V_$LOGMNR_LATCH
V$LOGMNR_TRANSACTION? ? ? ? ? Synonym for V_$LOGMNR_TRANSACTION
V$LOGMNR_REGION? ? ? ? ? ? ? ? Synonym for V_$LOGMNR_REGION
V$LOGMNR_CALLBACK? ? ? ? ? ? ? Synonym for V_$LOGMNR_CALLBACK
V$LOGMNR_SESSION? ? ? ? ? ? ? Synonym for V_$LOGMNR_SESSION
GV$LOGMNR_LOGFILE? ? ? ? ? ? ? Synonym for GV_$LOGMNR_LOGFILE
GV$LOGMNR_PROCESS? ? ? ? ? ? ? Synonym for GV_$LOGMNR_PROCESS
GV$LOGMNR_LATCH? ? ? ? ? ? ? ? Synonym for GV_$LOGMNR_LATCH
GV$LOGMNR_TRANSACTION? ? ? ? ? Synonym for GV_$LOGMNR_TRANSACTION
GV$LOGMNR_REGION? ? ? ? ? ? ? Synonym for GV_$LOGMNR_REGION
GV$LOGMNR_CALLBACK? ? ? ? ? ? Synonym for GV_$LOGMNR_CALLBACK
GV$LOGMNR_SESSION? ? ? ? ? ? ? Synonym for GV_$LOGMNR_SESSION
GV$LOGMNR_STATS? ? ? ? ? ? ? ? Synonym for GV_$LOGMNR_STATS
GV$LOGMNR_DICTIONARY_LOAD? ? ? Synonym for GV_$LOGMNR_DICTIONARY_LOAD
29 rows selected.
这里主要用到的是v$logmnr_contents这个视图，里面存放里挖掘日志获得的内容，来看一下表结构:
SQL> desc v$logmnr_contents;
?Name? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Null?? ? Type
?----------------------------------------------------------------