Oracle查询某时间段内归档日志生成量 - 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

72000688f352b7445f.png (115.68 KB, 下载次数: 240)

2025-8-3 18:08 上传

所以集群环境也查询v$archived_log即可

二、归档数量及大小查询
下面列出四种查询的情况

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代表天

29561688f3548acac0.png (599.38 KB, 下载次数: 208)

下载附件

2025-8-3 18:09 上传

上图可见每小时产生19G左右的归档，count(*)列代表每小时两节点一共产生了多少个归档日志。

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;

716688f35632b770.png (795.32 KB, 下载次数: 221)

下载附件

2025-8-3 18:09 上传

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;