归档数量及大小查询

下面列出四种查询的情况

1.查询当天每小时的归档日志生成量

alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

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;  

说明：
1、first_time 是归档日志的开头时间
2、DEST_ID指向本地归档，1代表log_archive_dest_1
3、trunc()函数中的hh代表小时，下面sql取dd代表天

2.查最近一周每天的归档日志生成量

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;

3.查询当天每小时的各个实例的归档日志生成量

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;

1. 查询最近一周每天的各个实例的归档日志生成量

   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;