Difference between revisions of "Calculate the size of archive log files each day"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
− | * Size of the archive log files. | + | * Size of the archive log files each day |
+ | <pre> | ||
+ | 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 | ||
+ | : | ||
+ | : | ||
+ | </pre> | ||
+ | * Size of the archive log files each hour. | ||
+ | <pre> | ||
+ | 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 | ||
+ | : | ||
+ | : | ||
+ | </pre> | ||
+ | * Size of the archive log files each day per instance. | ||
+ | <pre> | ||
+ | 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 | ||
+ | : | ||
+ | : | ||
+ | </pre> |
Revision as of 19:48, 12 March 2011
- 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 : :