1,创建按分钟分布的表
create table tmp_acq_min(m varchar2(10));
begin
for x in 0..23 loop
for y in 0..59 loop
insert into tmp_acq_min(m) values(lpad(x,2,'0')||lpad(y,2,'0'));
end loop;
end loop;
end;
/
commit;
2,加载日志数据
--创建日志表
create table tmp_acq(id int,d date,txt varchar2(4000),t timestamp);
--创建控制文件
acq.ctl:
LOAD DATA
APPEND
INTO TABLE TMP_ACQ
(id recnum,
txt char(2000)
)
--注:此处的recnum很有用,直接对应日志文件中的行号,对于skip,discard,reject的记录,仍然保留记录号;sequence,只在插入表的记录按序编号,为reject的记录保留需要。
--加载日志
sqlldr bnet303/bnet303 control=acq.ctl data=acqlog.2013-10-31.txt log=1031.log discard=1031.dis bad=1031.bad errors=999999999
3,获取记录时间
--获取时间
update tmp_acq
set t=to_timestamp(substr(txt, 1, 23), 'YYYY-MM-DD HH24:MI:SS,ff3')
where regexp_like(substr(txt, 1, 23),'\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d,\d\d\d');
--注:使用正则表达式,只更新符合格式的记录时间
create index idx_tmp_acq_1031 on tmp_acq_1031(id);
--对于部分报错信息,行首没有时间,更新为前一行的时间
declare
l_t timestamp;
begin
for x in(select * from tmp_acq order by id) loop
if(x.t is null) then
update tmp_acq a set a.t = l_t,d=sysdate where a.id = x.id;
end if;
if(x.t is not null) then
l_t := x.t;
end if;
end loop;
end;
/
commit;
4,查询
--O999:产品层报无法获取连接次数
select m.m, nvl(v.cnt, 0) cnt
from tmp_acq_min m,
(select to_char(t.t, 'HH24MI') t, count(*) cnt
from tmp_acq t
where t.txt like '%|ERROR|COMM|bnet.O999%'
group by to_char(t.t, 'HH24MI')) v
where m.m = v.t(+)
order by 1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-1069662/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-1069662/