批量分析日志

来源：互联网发布：支付宝登录淘宝被限制编辑：程序博客网时间：2024/06/07 04:58

有时需要大量分析日志，甚至上百个，如果一个个手动dbms_logmnr.add_logfile 无疑是一个很大工作量

于是写了存储过程进行批量分析

前提条件：sys用户建立

日志序列连续

----------------------------------------------------
-- Export file for user SYS --
-- Created by MI_Viewer on 2009-6-26, 下午 09:48:00 --
----------------------------------------------------

spool htliu.log

prompt
prompt Creating procedure MANUALLOGMIN
prompt ===============================
prompt
create or replace procedure sys.manuallogmin(startid in number,endid in number) is
logfile_name varchar2(200);
ccount number;
--n_record number;
currentId number;
begin
if (startid>endid) then
DBMS_OUTPUT.PUT_LINE('please input effect startid and endif....');
RETURN;
end if;
ccount:=(endid-startid)+1;
DBMS_OUTPUT.PUT_LINE(ccount);
currentId:=startid;
for i in 1..ccount loop
logfile_name:='D:/abc/REDo'||'0'||currentId||'.LOG';
DBMS_OUTPUT.PUT_LINE(logfile_name);
DBMS_OUTPUT.PUT_LINE('Begin deal with archlogfile='||logfile_name);
dbms_logmnr.add_logfile (LogFileName =>logfile_name,Options=>dbms_logmnr.new);
dbms_logmnr.start_logmnr (DictFileName =>'F:/oracleSer/oracle/product/10.2.0/log/logminer_dict.dat');
insert into mylog select SCN,TIMESTAMP,sql_redo from v$logmnr_contents where operation='DELETE'and table_name='IOMS_INTERIOR_TD';
insert into redolog_flag values(logfile_name,'1',redolog_sequence.nextval);
commit;
dbms_logmnr.end_logmnr;
--DBMS_LOCK.SLEEP(3);
currentId:=currentId+1;
end loop;
end manuallogmin;
/

spool off