下面给大家分享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)
执行结果
总结
以上所述是www.887551.com给大家介绍的oracle查询最近几天每小时归档日志产生数量的脚本写法,希望对大家有所帮助