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
--------- ---------- ----------
26-JAN-11          1 148411.591
26-JAN-11          2 145230.658
27-JAN-11          1 171689.525
27-JAN-11          2 88999.0806
28-JAN-11          1 168748.924
28-JAN-11          2 132560.147
29-JAN-11          1 153350.945
29-JAN-11          2 124917.091
30-JAN-11          1 39691.7251
30-JAN-11          2 48809.6528
31-JAN-11          1 125617.452
31-JAN-11          2 119977.382
01-FEB-11          1  46182.313
01-FEB-11          2 235871.665
02-FEB-11          1 39296.0962
02-FEB-11          2  84730.332
:
: