学习使用logminer

1.启用SUPPLEMENTAL LOG DATA
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

2. Extracting the Dictionary to a Flat File
（1）添加字典文件存放路径，将以下参数添加到初始化参数文件里
UTL_FILE_DIR = /oracle/database
(2)生成字典文件
 EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
'/oracle/admin/log', -
 OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

3.添加需要分析的redo文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oracle/arch/1_1022.dbf'-
,OPTIONS => DBMS_LOGMNR.NEW);

添加一个日志文件到分析队列中。
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/arch/1_160.dbf',-
OPTIONS => DBMS_LOGMNR.ADDFILE);

移除一个日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/arch/1_1020.dbf ', -
OPTIONS => DBMS_LOGMNR.REMOVEFILE);

4.启动日志分析
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME =>'/oracle/admin/log/dictionary.ora');

日志数据过滤：
只显示提交的数据
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME =>'/oracle/admin/log/dictionary.ora',-
OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

5.查询V$LOGMNR_CONTENTS，获取日志内容
SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS where username='SCOTT' AND SEG_NAME='DEMO';

6.停止日志分析
EXECUTE DBMS_LOGMNR.END_LOGMNR;