"Go" to see how much data Oracle generates archived logs per day and hourly

--View the amount of data generated per day for archived logs
Source: http://blog.51cto.com/bjiokn/321878

Alter session set nls_date_format= ' Yyyy.mm.dd hh24:mi:ss ';
Select Trunc (completion_time) as Arc_date,
COUNT (*) as Count,
Round ((SUM (blocks * block_size)/1024/1024), 2) as ARC_MB
From V$archived_log
Group by Trunc (Completion_time)
Order by Trunc (Completion_time);

--View the number of archived logs per hour in recent days
Source: 75309167

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), ' xx ', 1,0) H00,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' "1,0") H01,
SUM (DECODE (SUBSTR (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' To_char ', 1,0) H02,
SUM (DECODE (SUBSTR (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' To_char ', 1,0) H03,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' 1,0 ', H04,
SUM (DECODE (SUBSTR (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' To_char ', 1,0) H05,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' ", 1,0") H06,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' ", 1,0") H07,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' 1,0 '),
SUM (DECODE (SUBSTR (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' To_char ', 1,0) H09,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' Ten ', 1,0) H10,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' One ', ' 1,0 ') H11,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' 1,0 ', H12),
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H13,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H14,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' [] ', ' 1,0 ') H15,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H16,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H17,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H18,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H19,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' A ', ' 1,0 ') H20,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' + ', 1,0) H21,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' A ', ' 1,0 ') H22,
SUM (DECODE (SUBSTR (To_char (first_time, ' mm/dd/rr HH24:MI:SS '), 10,2), ' 23°c ', 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;

--Change the number of days to modify where First_time>=to_char (sysdate-11)

"Go" to see how much data Oracle generates archived logs per day and hourly