ORACLE LOGMINER使用简单步骤

1.安装LOGMNR包:
C:\>sqlplus /nolog
SQL> conn / as sysdba
SQL> @?/RDBMS/ADMIN/dbmslm.sql
SQL> @?/RDBMS/ADMIN/dbmslmd.sql
SQL> @?/RDBMS/ADMIN/dbmslms.sql
SQL> show parameter utl;

 
mkdir /home/oracle/logmnr
touch /home/oracle/logmnr/dictionary.ora

2.创建数据字典
SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=both;
SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora','/home/oracle/logmnr');

3.添加日志文件
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.new);
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.addfile);
或
SQL> begin
sys.dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',options =>dbms_logmnr.addfile);
end;

4.使用字典分析日志文件
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');

5.查询结果
SQL> select scn,sql_redo from v$logmnr_contents;
 
create table t as select * from v$logmnr_contents where table_name='XMAILDOMAIN';

6.退出logmnr
SQL> execute dbms_logmnr.end_logmnr;