查询归档量

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;