ITPub博客

首页 > 数据库 > Oracle > 归档日志大小统计

归档日志大小统计

原创 Oracle 作者:lwitpub 时间:2014-06-12 19:53:29 0 删除 编辑
我们可能经常需要评估每天或每小时产生的归档日志量,那么怎样计算出大小呢?
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    107
  • 访问量
    395481