Difference between revisions of "Calculate the size of archive log files each day"
Jump to navigation
Jump to search
(15 intermediate revisions by the same user not shown) | |||
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 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 | ||
+ | : | ||
+ | : | ||
+ | </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 | ||
+ | --------- ---------- ---------- | ||
+ | 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 | ||
+ | : | ||
+ | : | ||
+ | </pre> | ||
+ | == See Also == | ||
+ | * [[Log Switch History Matrix]] | ||
+ | |||
+ | [[Category:Oracle_Scripts]] |
Latest revision as of 17:41, 16 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 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 : :