--D:oracleproduct10.2.0log

--重启生效

alter system set utl_file_dir='D:/oracle/product/10.2.0/log' scope=spfile;

show parameter utl;

--创建LOGMNR包

--不要在plsql dev下面执行，直接在cmd下plsql下执行

@D:oracleproduct10.2.0db_3RDBMSADMINdbmslmd.sql

@D:oracleproduct10.2.0db_3RDBMSADMINdbmslm.sql

@D:oracleproduct10.2.0db_3RDBMSADMINdbmslms.sql

@D:oracleproduct10.2.0db_3RDBMSADMINprvtlm.plb

[@more@]

--生成字典

begin

sys.dbms_logmnr_d.build(dictionary_filename=>'myora100201.lgmr',dictionary_location=>'D:oracleproduct10.2.0log');

end;

/

--日志既可以为在线(online)重做日志，也可以为离线(offline)归档日志文件.在线重做日志可以从v$logfile获取,归档日志可以从v$archived_log获取

select * from v$logfile

EXECUTE sys.dbms_logmnr.add_logfile(logfilename=>'D:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG');

EXECUTE sys.dbms_logmnr.add_logfile(logfilename=>'D:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG',Options => dbms_logmnr.ADDFILE);

EXECUTE sys.dbms_logmnr.add_logfile(logfilename=>'D:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG',Options => dbms_logmnr.ADDFILE);

EXECUTE sys.dbms_logmnr.start_logmnr(DictFileName => 'D:/oracle/product/10.2.0/log/myora100201.lgmr');

create table testlog as SELECT * from V$LOGMNR_CONTENTS Order by 1

/*

select operation, timestamp,scn from v$logmnr_contents where seg_name = 'EMP' and seg_owner='SCOTT' and seg_type_name = 'TABLE';

select sql_redo,sql_undo from v$logmnr_contents where seg_name='EMP' and seg_owner='SCOTT' and seg_type_name = 'TABLE';

select usrnamen, session_info from logmnr_contents where seg_name = 'EMP' and seg_owner='SCOTT' and seg_type_name='TABLE';

*/

--终止

execute sys.dbms_logmnr.end_logmnr();

ITPub博客

oracle 10g logminer