Calculate the size of archive log files each day

• Size of the 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
------------- ----------
26-Jan-11     293642.249
27-Jan-11     260688.605
28-Jan-11     301309.071
29-Jan-11     278268.036
30-Jan-11     88501.3779
:
:

• Size of the archive log files each hour.

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH AM';

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_HOUR SIZE_IN_MB
------------------ ----------
26-Jan-11 0:00:00  13304.7856
26-Jan-11 1:00:00  7262.39208
26-Jan-11 2:00:00  4444.78613
26-Jan-11 3:00:00  7229.76367
26-Jan-11 4:00:00  9936.16552
26-Jan-11 5:00:00  6468.24316
26-Jan-11 6:00:00  8904.08740
26-Jan-11 7:00:00  11593.7358
26-Jan-11 8:00:00  20205.6821
26-Jan-11 9:00:00  20117.7275
26-Jan-11 10:00:00 18498.4887
26-Jan-11 11:00:00 17875.3457
26-Jan-11 12:00:00 7012.13427
26-Jan-11 1:00:00  8923.90136
26-Jan-11 2:00:00  13567.7656
26-Jan-11 3:00:00  5032.42382
:
:

• 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
--------- ---------- ----------
07-JAN-11          1 1.54638672
07-JAN-11          2 1.84716797
07-JAN-11          3 1.33398438
07-JAN-11          4 1.10986328
08-JAN-11          2 95.1162109
09-JAN-11          1 376.218262
09-JAN-11          2 .310546875
09-JAN-11          3 453.225098
09-JAN-11          4 436.769043
:
: