oracle 平均值 最大值,oracle 查询 归档日志最大值和平均值

select max(ss.size_GB), avg(ss.size_GB)

from (select s.*, rownum rn2

from (select a.*

from (select t1.*, rownum rn

from (SELECT TRUNC(FIRST_TIME) time,

SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 size_GB

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(FIRST_TIME)

order by time desc) t1) a

where a.rn > 1

order by a.time asc) s) ss

where ss.rn2 > 1;

----查询ASM使用率

select group_number,name,total_mb/1024 TGB,free_mb/1024 FGB,USABLE_FILE_MB/1024  from v$asm_diskgroup;

---RAC 查询单节点日归档大小

select to_char(b.FIRST_TIME, 'yyyy-mm-dd') arch_date,

round(sum(b.BLOCKS * b.BLOCK_SIZE / 1024 / 1024/1024), 2) "ARCH_SIZE(GB)"

from v$archived_log b

where b.CREATOR = 'ARCH'

and b.STANDBY_DEST = 'NO'

and b.archived = 'YES'

and b.dest_id = 1

and b.FIRST_TIME >= sysdate - 15

and b.FIRST_TIME < trunc(sysdate)

group by to_char(FIRST_TIME, 'yyyy-mm-dd')

order by 1;