Archivelogs Generated by Hour

SQL (Thread 1, last 7 days)

set linesize 145

set verify off

column h00   format a2  heading ' 0'
column h01   format a2  heading ' 1'
column H02   format a2  heading ' 2'
column H03   format a2  heading ' 3'
column H04   format a2  heading ' 4'
column H05   format a2  heading ' 5'
column H06   format a2  heading ' 6'
column H07   format a2  heading ' 7'
column H08   format a2  heading ' 8'
column H09   format a2  heading ' 9'
column H10   format a2  heading '10'
column H11   format a2  heading '11'
column H12   format a2  heading '12'
column H13   format a2  heading '13'
column H14   format a2  heading '14'
column H15   format a2  heading '15'
column H16   format a2  heading '16'
column H17   format a2  heading '17'
column H18   format a2  heading '18'
column H19   format a2  heading '19'
column H20   format a2  heading '20'
column H21   format a2  heading '21'
column H22   format a2  heading '22'
column H23   format a2  heading '23'
column TOTAL format 999 heading 'Tot'
column VRANK format 999 heading 'Rank'

prompt Instance 1
prompt ~~~~~~~~~~
select day,
       decode(h00,0,null,h00) h00,
       decode(h01,0,null,h01) h01,
       decode(h02,0,null,h02) h02,
       decode(h03,0,null,h03) h03,
       decode(h04,0,null,h04) h04,
       decode(h05,0,null,h05) h05,
       decode(h06,0,null,h06) h06,
       decode(h07,0,null,h07) h07,
       decode(h08,0,null,h08) h08,
       decode(h09,0,null,h09) h09,
       decode(h10,0,null,h10) h10,
       decode(h11,0,null,h11) h11,
       decode(h12,0,null,h12) h12,
       decode(h13,0,null,h13) h13,
       decode(h14,0,null,h14) h14,
       decode(h15,0,null,h15) h15,
       decode(h16,0,null,h16) h16,
       decode(h17,0,null,h17) h17,
       decode(h18,0,null,h18) h18,
       decode(h19,0,null,h19) h19,
       decode(h20,0,null,h20) h20,
       decode(h21,0,null,h21) h21,
       decode(h22,0,null,h22) h22,
       decode(h23,0,null,h23) h23,
       total, vrank
  from (
select to_char(trunc(first_time), 'Dy-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(*) total,
       rank () over (order by count(*)) vrank
  from v$log_history  a
 where (to_date(substr(to_char(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') >= trunc(sysdate-7))
   and (to_date(substr(to_char(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') <= trunc(sysdate-1))
   and thread# = 1
 group by trunc(first_time)
 order by trunc(first_time)
)
/

Sample Output

Day     0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23  Tot Rank
------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ---- ----
Wed-19    2  9  7  2  7  1  1  3     2  1  1  2  1     1     1        9  10 17   77    1
Thu-20 21 23 4  1  1  2     3  5  7  4  4  3  3  1  1        1        9  10 17  120    2
Fri-21 21 24 5  7  15 6  3  1  2  3     1  1                 1        17 32 29  168    7
Sat-22    1  6  6  14 7  2  3  2  2  1     1                 1        17 34 28  125    5
Sun-23    1  6  8  12 7  1  3  3  2  1     1                 1        17 33 28  124    4
Mon-24    2  7  15 9  6  3  3  1  1  1     1                 1        16 31 31  128    6
Tue-25    1  6  15 3  2        2  3  2  1  3  2  2           1        16 27 36  122    3