oracle logminer 实战

###1.修改utl_file_dir参数
 
SQL> alter system set UTL_FILE_DIR = 'D:/oracle/product/10.2.0/oradata';
 
alter system set UTL_FILE_DIR = 'D:/oracle/product/10.2.0/oradata'
 
ORA-02095: 无法修改指定的初始化参数 ###该参数要指定scope=spfile才能修改
 
SQL> alter system set UTL_FILE_DIR ='D:/oracle/product/10.2.0/oradata' scope=spfile;
 
System altered
 ###2.重启服务器以后再次登陆
SQL> conn wenhui/hbcrbt@wdb
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as wenhui
 
SQL> show parameter utl_file_dir
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      D:/oracle/product/10.2.0/oradata
 
SQL> conn sys/huaqin@wdb as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
 ###3.构建字典文件到平面文件
SQL> execute dbms_logmnr_d.build('logmnr_dic.ora','D:/oracle/product/10.2.0/oradata',options=>dbms_logmnr_d.store_in_flat_file);
 
PL/SQL procedure successfully completed
 ###4.添加要分析的日志文件
SQL> select member from v$logfile;
 
MEMBER
--------------------------------------------------------------------------------
D:/ORACLE/PRODUCT/10.2.0/ORADATA/WDB/REDO03.LOG
D:/ORACLE/PRODUCT/10.2.0/ORADATA/WDB/REDO02.LOG
D:/ORACLE/PRODUCT/10.2.0/ORADATA/WDB/REDO01.LOG
 
SQL> execute dbms_logmnr.add_logfile(LogFileName => 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/WDB/REDO01.LOG',options=>dbms_logmnr.addfile);
 
PL/SQL procedure successfully completed
 ###5.开始分析
SQL> execute dbms_logmnr.start_logmnr(DictFileName => 'D:/oracle/product/10.2.0/oradata/logmnr_dic.ora');
 
PL/SQL procedure successfully completed
 ###6.通过查询视图分析日志文件
SQL> select count(0) from v$logmnr_contents;
 
  COUNT(0)
----------
     72597
 
SQL> select a.TABLE_NAME from v$logmnr_contents a where a.USERNAME='WENHUI';
 
TABLE_NAME
--------------------------------
LINK$
 
6 rows selected
 
SQL> select a.* from v$logmnr_contents a where a.USERNAME='WENHUI';
 
##内容有点乱，删除掉了哈。。大家自己可以试一下，查询的结果是用户WENHUI的所有操作
 
6 rows selected
 ###7.分析结束
SQL> execute dbms_logmnr.end_logmnr;
 
PL/SQL procedure successfully completed