我们可能经常需要评估每天或每小时产生的归档日志量，那么怎样计算出大小呢？
So How can we calculate archivelog size each day/hour？
主要还是查询视图V$ARCHIVED_LOG，分享一下这几个SQL:
1，Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
2，Archivelog size each hour:
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
Session altered.
SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;
3， another example，
SQL> SELECT to_char(completion_time,'YYYYMMDD') run_date, Round(Sum(blocks * block_size + block_size) / 1024 / 1024 / 1024) redo_blocks
FROM v$archived_log GROUP BY To_char(completion_time,'YYYYMMDD') ORDER BY 2;
这里注释：
V$ARCHIVED_LOG contains BLOCKS ( Size of the archived log (in blocks) ) and BLOCK_SIZE ( which is the same as the logical block size of the online log from which the archived log was copied )
So How can we calculate archivelog size each day/hour？
主要还是查询视图V$ARCHIVED_LOG，分享一下这几个SQL:
1，Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
2，Archivelog size each hour:
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
Session altered.
SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;
3， another example，
SQL> SELECT to_char(completion_time,'YYYYMMDD') run_date, Round(Sum(blocks * block_size + block_size) / 1024 / 1024 / 1024) redo_blocks
FROM v$archived_log GROUP BY To_char(completion_time,'YYYYMMDD') ORDER BY 2;
这里注释：
V$ARCHIVED_LOG contains BLOCKS ( Size of the archived log (in blocks) ) and BLOCK_SIZE ( which is the same as the logical block size of the online log from which the archived log was copied )
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29674916/viewspace-2127910/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
