logminer

--归档模式下使用
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
--使用一般文件存储数据字典,必须在归档模式下
execute dbms_logmnr_d.build('logmnr_dict','/home/oracle');
--加入日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo03.log',options=>dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo04.log',options=>dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo05.log',options=>dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo06.log',options=>dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo02.log',options=>dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo09.log',options=>dbms_logmnr.ADDFILE);
--查询日志文件的
alter session set nls_date_format='yyyymmddhh24mi';
select log_id,filename,db_name ,status,b.LOW_TIME,b.HIGH_TIME,b.low_scn,b.next_scn from v$logmnr_logs b;
--在flat文件中生成数据字典
exec dbms_logmnr.start_logmnr(DictFileName => '/home/oracle/logmnr_dict');
--开始挖掘数据
EXECUTE DBMS_LOGMNR.START_LOGMNR(options=>dbms_logmnr.DICT_FROM_ONLINE_CATALOG+dbms_logmnr.PRINT_PRETTY_SQL,startTime => to_date('201111070000','yyyymmddhh24mi'),endTime => to_date('201111081411','yyyymmddhh24mi'));
--查看日志中的内容
select t.timestamp,t.sql_redo,t.sql_undo,t.seg_owner from v$logmnr_contents t where t.SEG_OWNER='SCOTT';
--把日志中的会话内容copy到其他表，防止发生改变
create table scott.logmnr_contents as select * from v$logmnr_contents;
exec dbms_logmnr.end_logmnr;[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1056215/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1056215/