在O racle数据库中，通过v$archived_log数据字典视图查询该数据库的归档日志文件的生成情况。 如果你以为在rac下需要查的gv$archvied_log视图，这其实是一个错误的想法。 无论在单实例数据库，还是多实例的RAC数据库，都是查这个视图来获取信息。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1749746 )
查当天每小时的归档日志生成量
Sql代码 
- select logtime,
- count(*),
- round(sum(blocks * block_size) / 1024 / 1024) mbsize
- from (select trunc(first_time, 'hh') as logtime, a.BLOCKS, a.BLOCK_SIZE
- from v$archived_log a
- where a.DEST_ID = 1
- and a.FIRST_TIME > trunc(sysdate))
- group by logtime
- order by logtime desc;
查最近一周每天的归档日志生成量
Sql代码
- select logtime,
- count(*),
- round(sum(blocks * block_size) / 1024 / 1024) mbsize
- from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE
- from v$archived_log a
- where a.DEST_ID = 1
- and a.FIRST_TIME > trunc(sysdate - 7))
- group by logtime
- order by logtime desc;
如果你需要知道RAC下各个节点的归档日志情况，我将上面脚本略作修改，增加thread#列。
查当天每小时的各个实例的归档日志生成量
Sql代码
- select THREAD#,
- logtime,
- count(*),
- round(sum(blocks * block_size) / 1024 / 1024) mbsize
- from (select a.THREAD#,
- trunc(first_time, 'hh') as logtime,
- a.BLOCKS,
- a.BLOCK_SIZE
- from v$archived_log a
- where a.DEST_ID = 1
- and a.FIRST_TIME > trunc(sysdate))
- group by THREAD#, logtime
- order by THREAD#, logtime desc;
查最近一周每天的各个实例的归档日志生成量
Sql代码
- select THREAD#,
- logtime,
- count(*),
- round(sum(blocks * block_size) / 1024 / 1024) mbsize
- from (select THREAD#,
- trunc(first_time, 'dd') as logtime,
- a.BLOCKS,
- a.BLOCK_SIZE
- from v$archived_log a
- where a.DEST_ID = 1
- and a.FIRST_TIME > trunc(sysdate - 7))
- group by THREAD#, logtime
- order by THREAD#, logtime desc;
敬请留存，以备不时之需 @mikixiyou
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/500314/viewspace-1063684/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
