oracle日志挖掘 - 追踪某个时间段的SQL语句

一、DML日志挖掘

0）查找归档文件时间

select name, first_time, thread#, sequence# from v$archived_log order by 2;

1）添加database补充日志

alter database add supplemental log data;

2）确定要分析的日志范围，添加日志，分析

execute dbms_logmnr.add_logfile(logfilename=>'日志',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'追加日志',options=>dbms_logmnr.addfile);

3）执行logmnr 分析

execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4）查询分析结果

select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='表名';  

5）关闭日志分析

execute dbms_logmnr.end_logmnr;     

补充
打开数据库级别的最小补全日志

alter database add supplemental log data;

查看是否开启

select supplemental_log_data_min min from v$database;

关闭最小补全日志

alter database drop supplemental log data;

二、DDL挖掘

1）建立一个存放dict.oral的目录，设置参数utl_file_dir指定该目录

alter system set utl_file_dir='/...' scope=spfile;   

2）建立数据字典文件dict.ora

execute dbms_logmnr_d.build('dict.ora','/...',dbms_logmnr_d.store_in_flat_file);

3）添加日志分析

execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'追加日志',options=>dbms_logmnr.addfile);

4）执行分析

execute dbms_logmnr.start_logmnr(dictfilename=>'/.../dict.ora',options=>dbms_logmnr.ddl_dict_tracking);    

5）查看分析结果

select username,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents WHERE USERNAME ='用户名' and lower(sql_redo) like '%表名%';

6）关闭日志分析

execute dbms_logmnr.end_logmnr;     

测试

• Oracle日志挖掘 理论
• Oracle日志挖掘 LogMiner 实践
• Oracle日志挖掘之LogMiner 理论+实践