Query to count hourly log switch in oracle

SQL> set lines 120;
set pages 999;
SQL> SQL>
SQL> SELECT
2 to_char(first_time,'YYYY-MON-DD') day,
3 to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
4 to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
5 to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
6 to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
7 to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
8 to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
9 to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
10 to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
11 to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
12 to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
13 to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
14 to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
15 to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
16 to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
17 to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
18 to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
19 to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
20 to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
21 to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
22 to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
23 to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
24 to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
25 to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
26 to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
27 from
28 v$log_history
29 GROUP by
30 to_char(first_time,'YYYY-MON-DD') ;

DAY 00 01 02 03 04 05 06 07 0 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2017-JUL-06 1 0 0 0 2 0 1 2 0 7 36 45 37 43 37 44 50 37 22 9 2 1 1 0
2017-JUL-07 1 0 0 0 0 0 1 1 1 4 22 29 24 42 33 40 49 67 28 14 4 2 0 0
2017-JUL-08 0 0 0 1 0 0 1 0 0 0 1 6 7 5 3 4 3 1 3 0 0 1 1 0
2017-JUL-05 0 0 0 0 0 1 1 0 0 0 0 60 57 56 37 48 71 69 40 19 4 0 0 0
2017-JUL-13 1 1 0 0 0 0 0 1 0 5 18 23 28 29 20 27 47 32 20 8 0 1 1 0
2017-JUL-09 1 0 0 0 0 0 1 1 0 0 0 0 1 2 0 2 0 0 2 1 0 0 0 0
2017-JUL-11 2 0 0 0 0 0 1 0 2 4 22 33 27 27 22 34 40 44 26 11 3 0 0 0
2017-JUL-10 1 0 0 0 0 0 1 0 0 2 18 20 32 24 25 29 41 45 26 12 3 1 1 0
2017-JUL-12 2 0 0 0 0 0 1 0 1 3 17 30 24 26 21 34 44 34 25 16 3 1 0 0
2017-JUL-14 0 0 0 0 0 0 2 0 0 3 16 15 20 16 16 22 28 28 16 5 1 0 2 0
2017-JUL-16 1 0 0 0 0 1 0 0 1 1 0 0 1 0 1 0 1 0 0 1 0 0 0 0
2017-JUL-15 0 0 0 0 0 0 1 0 0 3 14 26 23 24 13 20 28 22 16 3 3 0 0 0
2017-JUL-17 2 0 0 0 0 0 1 0 0 2 8 28 17 26 35 0 0 0 0 0 0 0 0 0
2017-JUL-04 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 21 6 2 2 0

14 rows selected.


col day format a15;
col hour format a4;
col total format 999;

select
to_char(first_time,'yyyy-mm-dd') day,
to_char(first_time,'hh24') hour,
count(*) total
from v$log_history
group by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24') asc;

DAY HOUR TOTAL
--------------- ---- -----
2017-07-14 15 22
2017-07-14 16 28
2017-07-14 17 28
2017-07-14 18 16
2017-07-14 19 5
2017-07-14 20 1
2017-07-14 22 2
2017-07-15 06 1
2017-07-15 09 3
2017-07-15 10 14
2017-07-15 11 26

DAY HOUR TOTAL
--------------- ---- -----
2017-07-15 12 23
2017-07-15 13 24
2017-07-15 14 13
2017-07-15 15 20
2017-07-15 16 28
2017-07-15 17 22
2017-07-15 18 16
2017-07-15 19 3
2017-07-15 20 3
2017-07-16 00 1
2017-07-16 05 1

DAY HOUR TOTAL
--------------- ---- -----
2017-07-16 08 1
2017-07-16 09 1
2017-07-16 12 1
2017-07-16 14 1
2017-07-16 16 1
2017-07-16 19 1
2017-07-17 00 2
2017-07-17 06 1
2017-07-17 09 2
2017-07-17 10 8
2017-07-17 11 28

DAY HOUR TOTAL
--------------- ---- -----
2017-07-17 12 17
2017-07-17 13 26
2017-07-17 14 35

179 rows selected.