--查看90天以来日志量的产生情况
col h00 for 99
col h01 for 99
col h02 for 99
col h03 for 99
col h04 for 99
col h05 for 99
col h06 for 99
col h07 for 99
col h08 for 99
col h09 for 99
col h10 for 99
col h11 for 99
col h12 for 99
col h13 for 99
col h14 for 99
col h15 for 99
col h16 for 99
col h17 for 99
col h18 for 99
col h19 for 99
col h20 for 99
col h21 for 99
col h22 for 99
col h23 for 99
col total for 999
col avg for 99.99
select to_char(first_time,'mm/dd') day, sum(decode(to_char(first_time, 'hh24'),'00',1,0)) h00,
sum(decode(to_char(first_time, 'hh24'),'01',1,0)) h01, sum(decode(to_char(first_time, 'hh24'),'02',1,0)) h02,
sum(decode(to_char(first_time, 'hh24'),'03',1,0)) h03, sum(decode(to_char(first_time, 'hh24'),'04',1,0)) h04,
sum(decode(to_char(first_time, 'hh24'),'05',1,0)) h05, sum(decode(to_char(first_time, 'hh24'),'06',1,0)) h06,
sum(decode(to_char(first_time, 'hh24'),'07',1,0)) h07, sum(decode(to_char(first_time, 'hh24'),'08',1,0)) h08,
sum(decode(to_char(first_time, 'hh24'),'09',1,0)) h09, sum(decode(to_char(first_time, 'hh24'),'10',1,0)) h10,
sum(decode(to_char(first_time, 'hh24'),'11',1,0)) h11, sum(decode(to_char(first_time, 'hh24'),'12',1,0)) h12,
sum(decode(to_char(first_time, 'hh24'),'13',1,0)) h13, sum(decode(to_char(first_time, 'hh24'),'14',1,0)) h14,
sum(decode(to_char(first_time, 'hh24'),'15',1,0)) h15, sum(decode(to_char(first_time, 'hh24'),'16',1,0)) h16,
sum(decode(to_char(first_time, 'hh24'),'17',1,0)) h17, sum(decode(to_char(first_time, 'hh24'),'18',1,0)) h18,
sum(decode(to_char(first_time, 'hh24'),'19',1,0)) h19, sum(decode(to_char(first_time, 'hh24'),'20',1,0)) h20,
sum(decode(to_char(first_time, 'hh24'),'21',1,0)) h21, sum(decode(to_char(first_time, 'hh24'),'22',1,0)) h22,
sum(decode(to_char(first_time, 'hh24'),'23',1,0)) h23,
count(*)||'('||trim(to_char(sum(blocks*block_size)/1024/1024,'99,999.9'))||'M)' TOTAL
from (select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time from
v$archived_log a where completion_time > trunc(sysdate - 90)
--and dest_id = 1
group by sequence#)
group by to_char(first_time,'mm/dd'), to_char(first_time,'yyyy/mm/dd')
order by to_char(first_time,'yyyy/mm/dd') desc
|
