欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle查询最近几天每小时归档日志产生数量的脚本写法

程序员文章站 2022-03-16 20:15:34
下面给大家分享oracle查询最近几天每小时归档日志产生数量的脚本写法,脚本如下所示: select substr(to_char(first_time, 'mm...

下面给大家分享oracle查询最近几天每小时归档日志产生数量的脚本写法,脚本如下所示:

select substr(to_char(first_time, 'mm/dd/rr hh:mi:ss'),1,5) day,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'00',1,0)) h00,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'01',1,0)) h01,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'02',1,0)) h02,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'03',1,0)) h03,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'04',1,0)) h04,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'05',1,0)) h05,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'06',1,0)) h06,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'07',1,0)) h07,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'08',1,0)) h08,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'09',1,0)) h09,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'10',1,0)) h10,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'11',1,0)) h11,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'12',1,0)) h12,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'13',1,0)) h13,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'14',1,0)) h14,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'15',1,0)) h15,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'16',1,0)) h16,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'17',1,0)) h17,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'18',1,0)) h18,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'19',1,0)) h19,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'20',1,0)) h20,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'21',1,0)) h21,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'22',1,0)) h22,
    sum(decode(substr(to_char(first_time, 'mm/dd/rr hh24:mi:ss'),10,2),'23',1,0)) h23,
    count(*) total
from v$log_history a
where first_time>=to_char(sysdate-10)
group by substr(to_char(first_time, 'mm/dd/rr hh:mi:ss'),1,5)
order by substr(to_char(first_time, 'mm/dd/rr hh:mi:ss'),1,5) desc;

修改天数,可以修改where first_time>=to_char(sysdate-11) 

执行结果

Oracle查询最近几天每小时归档日志产生数量的脚本写法

总结

以上所述是小编给大家介绍的oracle查询最近几天每小时归档日志产生数量的脚本写法,希望对大家有所帮助