Oracle查询某时间段内归档日志生成量

--查每天产生归档日志大小
alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select trunc(completion_time) as ARC_DATE,
count(*) as COUNT,
round((sum(blocks * block_size) / 1024 / 1024), 2) as ARC_MB
from v$archived_log
group by trunc(completion_time)
order by trunc(completion_time) desc;


--查最近一周每天的归档日志生成量
alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) size_mb
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;


--查每小时产生归档日志大小
alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) size_mb
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;


--查询当天每小时各个实例归档日志生成量
alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select thread#,
logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) size_mb
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;


--查询最近一周每天各个实例归档日志生成量
alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select thread#,
logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) size_mb
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;