ogmnr的使用:
1、在oracle8i中要首先运行dbmslsm.sql和dbmslsmd.sql
如果报错:
dbmslmd.sql 中修改
TYPE col_desc_array IS VARRAY(513) OF col_description;
成
TYPE col_desc_array IS VARRAY(700) OF col_description;
2、修改初始化参数utl_file_dir为d:\oracle\log;
3、在sqlplus中运行
EXECUTE dbms_logmnr_d.build(
dictionary_filename => ' mydb_dictionary.ora',
dictionary_location => 'd:\oracle\log',options=>dbms_logmnr_d.store_in_flat_file);
4、执行
EXECUTE dbms_logmnr.add_logfile(
LogFileName=>' d:\Oracle\oradata\demo\redo01.log',
Options=>dbms_logmnr.new);
或者
EXECUTE dbms_logmnr.add_logfile(
LogFileName=>' d:\Oracle\oradata\demo\redo02.log',
Options=>dbms_logmnr.addfile);
5、开始分析
EXECUTE dbms_logmnr.start_logmnr(
DictFileName=>' d:\oracle\log\mydb_dictionary.ora ',
startTime=>to_date('01-JUNE-2001 12:30:00','DD-MON-YYYY HH24:MI:SS'),endtime=>to_date('01-JUNE-2001 17:30:00','DD-MON-YYYY HH24:MI:SS'));
或者
EXECUTE dbms_logmnr.start_logmnr(
DictFileName=>' d:\oracle\log\mydb_dictionary.ora ',
startscn=>125,endscn=>200);
或者不加筛选:
EXECUTE dbms_logmnr.start_logmnr(
DictFileName=>' d:\oracle\log\mydb_dictionary.ora ')
6、查询分析结果:
SELECT sql_redo FROM v$logmnr_contents WHERE username=scott' AND tablename='table1';
7、结束当前分析:
DBMS_LOGMNR.END_LOGMNR
|