oracle归档日志百分比,归档日志大小统计

我们可能经常需要评估每天或每小时产生的归档日志量，那么怎样计算出大小呢？

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博客 ” ，链接：http://blog.itpub.net/628922/viewspace-1181672/，如需转载，请注明出处，否则将追究法律责任。