当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle查询最近几天每小时归档日志产生数量的脚本写法

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

2017年12月08日  | 移动技术网IT编程  | 我要评论

大剧盛典,丑女代嫁,经典闽南歌曲

下面给大家分享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查询最近几天每小时归档日志产生数量的脚本写法，希望对大家有所帮助

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网