分析oracle侦听日志-凯发app官方网站

create table listener_log
(
rq        varchar2(30),
conn      varchar2(400),
address   varchar2(400),
action    varchar2(20),
service   varchar2(20),
retu_code varchar2(10),
host      varchar2(50)
);

用 sql loader 加载侦听日志到表中

控制文件c.txt 内容：

load data
infile 'd:\lis.log'
truncate
into table listener_log
when (conn <> ' service_update ')
fields terminated by "*"
trailing nullcols
(rq,conn,address,action,service,retu_code,host)

加载命令：
sqlldr scott/tiger control=d:\c.txt direct=true

--查询某天每小时连接数：
select substr(rq,-9,2) hh,count(0) cnt
from listener_log
where rq like '22-mar-2011%'
group by substr(rq,-9,2)
order by hh;

用pl/sql developer可以生成柱状图，比较直观:

--查询每天的连接情况：
select substr(rq,1,6) dd,count(0) cnt
from listener_log
group by substr(rq,1,6)
order by dd;

--查看各 program 连接数：
select program, count(0)
from (select substr(conn,
        instr(conn, 'program') 8,
        instr(substr(conn, instr(conn, 'program') 8), ')') - 1) program
          from listener_log
         where conn like '%program%')
group by program;

--查看各 host 的连接情况：

select host, count(0)
from (select substr(address,
        instr(address, 'host') 5,
        instr(substr(address, instr(address, 'host') 5), ')') - 1) host
          from listener_log
         where conn like '%host%')
group by host;

结束。

以下内容无用：

--加载完后，可以将不要的数据进行剔除
select * from listener_log;

delete from listener_log

where conn=' ping '

or address=' status '

or address =' services ';

commit;

--整理数据，将 host 列出来：
update listener_log set host=
substr(address, instr(address, 'host=') length('host='),
instr(address, '(port=') - instr(address, 'host=')-length('(port=')) ;

commit;

--查看各 ip 连接情况：
select host, count(0) cnt
from listener_log
where rq like '24-mar-2011%'
group by host;

阅读(2276) | 评论(0) | 转发(0) |

上一篇：每组前n条记录 sql

下一篇：oracle extent及pctfree的总结

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们