SQL> @/dat/DBSoftware/oracle/product/10.2.0/db_1/rdbms/admin/dbmslm.sql------用来创建DBMS_LOGMNR包，该包用来分析日志文件

Package created.

Grant succeeded.

SQL> @/dat/DBSoftware/oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd.sql------用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。 

Package created.

SQL> alter database add supplemental log data;----------如果不启用日志追加，就无法查看DML，只能查看DDL

Database altered.

创建数据字典文件

数据字典文件是一个文本文件，由dbms_logmnr_d来创建。

首先需要修改参数UTL_FILE_DIR。

该参数值为服务器中放置数据字典文件的目录，10g中通过动态修改参数的方式来修改，然后重启数据库方生效。其中该目录必须预先建立好。

SQL> show parameter utl_file_dir;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string

[oracle@localhost ~]$ mkdir log

SQL> alter system set UTL_FILE_DIR='/home/oracle/log' scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  419430400 bytes

Fixed Size                  1267836 bytes

Variable Size             364906372 bytes

Database Buffers           50331648 bytes

Redo Buffers                2924544 bytes

Database mounted.

Database opened.

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string      /home/oracle/log

然后创建数据字典文件

SQL> begin 

dbms_logmnr_d.build(dictionary_filename=>'logminer_dict.ora',dictionary_location=>'/home/oracle/log');

end; 

 /

PL/SQL procedure successfully completed.

创建要分析的日志文件（在线日志或归档日志）的列表

SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/dat/DBData/oradata/NACEC/redo01.log',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/dat/DBData/oradata/NACEC/redo02.log',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/dat/DBData/oradata/NACEC/redo03.log',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

使用Logminer进行日志分析

SQL> execute dbms_logmnr.start_logmnr(DictFileName=>'/home/oracle/log/logminer_dict.ora');

PL/SQL procedure successfully completed.

SQL> select username ,operation,sql_redo,sql_undo,SCN

from v$logmnr_contents

where seg_owner='LIUSHIMING’;

SQL> delete from bigtable where rownum<1000;

999 rows deleted.

SQL> commit;

Commit complete.

SQL> select username ,operation,sql_redo,sql_undo,SCN

from v$logmnr_contents

where seg_owner='LIUSHIMING’AND OPERATION=‘DELETE’;

no rows selected

如果只能看到DDL语句，看不到DML语句，执行

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); ----只分析当前数据库的重做日志文件