分析归档日志提取某些对象被修改的记录

最新推荐文章于 2022-02-14 15:07:31 发布

甲骨灰

最新推荐文章于 2022-02-14 15:07:31 发布

阅读量836

点赞数

分类专栏： ORACLE 文章标签：分析归档

本文链接：https://blog.csdn.net/XmallWood/article/details/39961989

版权

ORACLE 专栏收录该内容

45 篇文章 0 订阅

订阅专栏

1
SQL> show parameter UTL_FILE
NAME TYPE
------------------------------------ -------------
VALUE
------------------------------
utl_file_dir string
e:\oracle\product\10.2.0\oradata\test\logs_utl_file
SQL>
2
然后创建数据字典文件：
EXECUTE dbms_logmnr_d.build( 'dict20130402.dat', '/oracle/oradmin/logmnr',dbms_logmnr_d.store_in_flat_file);
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('EHR') ;

execute dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/oradata/jfehr/redo01.log',options=>dbms_logmnr.new);

execute dbms_logmnr.start_logmnr(DictFileName=> '/oracle/oradmin/logmnr/dict20130402.dat');

5 query:
SELECT SQL_REDO FROM v$logmnr_contents WHERE username='JFEHR'

6 create table yxm_xx1 as select * from v$logmnr_contents where 1=2 ;

7 create table yxm_xx2 (arcfilename varchar(200)); insert archive log file name in this table;

8
begin
for cc in (select * from jfehr.yxm_xx2) loop
dbms_logmnr.add_logfile(LogFileName=>'/data/archivelog/'||cc.arcfilename,options=>dbms_logmnr.new);
dbms_logmnr.start_logmnr(DictFileName=> '/data/logminer/dict20141008.dat');
execute immediate ('insert into jfehr.yxm_xx1 SELECT * FROM v$logmnr_contents WHERE username=''JFEHR'' and SQL_REDO like upper(''%rew_rewtotal%'')');
commit;
end loop;
end;
9
select * from yxm_xx1 ;