使用LogMiner-数据库专栏,SQL Server

1.创建dbms_logmnr包
sql>@$oracle_home/rdbms/admin/dbmslm.sql

2.创建相关数据字典
sql>@$oracle_home/rdbms/admin/dbmslmd.sql

3.修改初始化参数utl_file_dir,指定分析数据的存放处
sql>alter system set utl_file_dir=/opt/oracle/logminer scope=spfile;

4.重启数据库
sql>shutdown immediate
sql>startup

5.创建数据字典文件

sql>execute dbms_logmnr_d.build(dictionary_filename =>sqltrace.ora,dictionary

_location =>/opt/oracle/logminer/log);

6.建立日志分析表

sql> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>/opt/oracle/ora92/rdbms/arc00009.001);

7.添加用于分析的日志文件

sql> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename=>/opt/oracle/ora92/rdbms/arc00010.001);

sql> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>/opt/oracle/ora92/rdbms/arc00011.001);

删除

sql> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilenam

e =>/opt/oracle/ora92/rdbms/arc00011.001);

 8.启动logminer进行分析

sql> execute dbms_logmnr.start_logmnr(dictfilename =>/opt/oracle/logminer/log/sqltrace.or

a,starttime =>to_date(20040625 09:00:00,yyyymmdd hh24:mi:ss),endtime =>to_d

ate(20040625 22:00:00,yyyymmdd hh24:mi:ss));

9.参看分析结果

select operation,sql_redo,sql_undo from v$logmnr_contents;

 
10.结束分析

sql> execute dbms_logmnr.end_logmnr;

一旦结束视图v$logmnr_contents中的分析结果也随之不再存在。