今天实验内容是日志挖掘工具LOGMNR的使用，我的测试版本是10.2.0.1默认就自带了，无需另外安装。
如果未安装过提示无法使用这个dbms包，则可以用SYSDBA登陆，然后依次执行：
@$Oracle_HOME\rdbms\admin\dbmslm.sql;
@$ORACLE_HOME\rdbms\admin\dbmslmd.sql;
第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。
第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。
--开始实验
[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这个视图，里面存放里挖掘日志获得的内容，来看一下表结构: