SQL Query to find out archive log generation rate of last 30 days

Using this SQL Query we can find out number archive log generated per hour in last 30 days..

I used in where clause  "sysdate - 30" to find out the result of last 30 days. You can change the number of days as per your requirement. 

set echo off; set pages 10000 set sqlbl on; col day for a12 set lines 1000 set pages 999 col "00" for a3 col "01" for a3 col "02" for a3 col "03" for a3 col "04" for a3 col "05" for a3 col "06" for a3 col "07" for a3 col "08" for a3 col "09" for a3 col "10" for a3 col "11" for a3 col "12" for a3 col "13" for a3 col "14" for a3 col "15" for a3 col "16" for a4 col "17" for a3 col "18" for a4 col "19" for a3 col "20" for a3 col "21" for a3 col "22" for a3 col "23" for a3   SELECT to_char(first_time,'DD-MON-YYYY') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23", count(*) Total from gv$log_history WHERE first_time > sysdate -7 GROUP by to_char(first_time,'DD-MON-YYYY'),trunc(first_time) order by trunc(first_time);

If there is a standby database configured, using the below query it would show the total number of archivelog generation for both primary and standby locations.

set lines 300 col 00 format A5 col 01 format A5 col 02 format A5 col 03 format A5 col 04 format A5 col 05 format A5 col 06 format A5 col 07 format A5 col 08 format A5 col 09 format A5 col 10 format A5 col 11 format A5 col 12 format A5 col 13 format A5 col 14 format A5 col 15 format A5 col 16 format A5 col 17 format A5 col 18 format A5 col 19 format A5 col 20 format A5 col 21 format A5 col 22 format A5 col 23 format A5   SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22 Hr", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23 Hr" from v$archived_log where to_date(first_time) > sysdate - 30 GROUP by to_char(first_time,'YYYY-MON-DD'), to_date(first_time) order by to_date(first_time);

The query output looks like this