How to Calculate the size of archive log files each day in Oracle Database

Calculate the size of archive log files each day

SQL> SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
           SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
      FROM V$ARCHIVED_LOG
     GROUP BY TRUNC(COMPLETION_TIME)
     ORDER BY 1;
 
ARCHIVED_DATE SIZE_IN_MB
------------- ----------
2020-02-14 00:00:00 2406.45117
2020-02-15 00:00:00 3491.56445
2020-02-16 00:00:00 3196.87354
2020-02-17 00:00:00 2917.70605
2020-02-18 00:00:00 2798.62158
2020-02-19 00:00:00 2650.23242

Size of the archive log files each hour.

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
Session altered.

SQL> SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME, 'HH')
      ORDER BY 1;

ARCHIVED_DATE SIZE_IN_MB
------------- ----------
2020-01-21 02 13304.7856

Size of the archive log files each day per instance

SQL> SELECT
            TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
            THREAD#,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME), THREAD#
      ORDER BY 1, 2;


ARCHIVED_    THREAD# SIZE_IN_MB
--------- ---------- ----------
26-JAN-20          1 148411.591