Calculate the size of archive log files each day
Jump to navigation
Jump to search
- 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 : :