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 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
------------- ----------
2011-01-26 00 13304.7856
2011-01-26 01 7262.39209
2011-01-26 02 4444.78613
2011-01-26 03 7229.76367
2011-01-26 04 9936.16553
2011-01-26 05 6468.24316
2011-01-26 06  8904.0874
2011-01-26 07 11593.7358
2011-01-26 08 20205.6821
2011-01-26 09 20117.7275
2011-01-26 10 18498.4888
2011-01-26 11 17875.3457
2011-01-26 12 7012.13428
2011-01-26 13 8923.90137
2011-01-26 14 13567.7656
2011-01-26 15 5032.42383
2011-01-26 16 17356.7549
2011-01-26 17 26535.3154
2011-01-26 18 15555.6626
2011-01-26 19 8450.63184
2011-01-26 20 7845.36865
2011-01-26 21 10421.9067
2011-01-26 22 11770.5005
2011-01-26 23 15328.6797
:
:

• 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
:
:

See Also

• Log Switch History Matrix