set linesize 100
col hostname      format a12 TRUNC head 'Serveur'
col instance_name format a10 TRUNC head 'Instance'
col arch          format 9999999 TRUNC head 'Archive 3j (Mo)'

select  host_name hostname,
        instance_name,
        max(taille) / 1024 / 1024 arch from
v$instance,
(
    select jour, sum(vol) over (order by jour rows 2 preceding) taille
    from
    (
        select sum(BLOCKS*BLOCK_SIZE) vol, to_char(completion_time,'YYYY/MM/DD') jour
        from v$archived_log  where completion_time >= sysdate - 31
        group by to_char(completion_time,'YYYY/MM/DD')
    )
)
group by host_name, instance_name;

set linesize 100
set pagesize 1000
col arch          format 9999999 TRUNC head 'Arch max (8 jours) Mo'

select max(taille) arch
from (
select  sum(BLOCKS*BLOCK_SIZE) / 1024 / 1024 taille,
        to_char(completion_time,'YYYY/MM/DD') jour
from v$archived_log
where completion_time >= sysdate - 8
group by to_char(completion_time,'YYYY/MM/DD')
);


col arch          format 9999999 TRUNC head 'Arch max (31 jours) Mo'

select max(taille) arch
from (
select  sum(BLOCKS*BLOCK_SIZE) / 1024 / 1024 taille,
        to_char(completion_time,'YYYY/MM/DD') jour
from v$archived_log
where completion_time >= sysdate - 31
group by to_char(completion_time,'YYYY/MM/DD')
);



set linesize 100
set pagesize 1000
col arch          format 9,999,999 TRUNC head 'Arch (Mo)'
col jour          format a10 TRUNC head 'Jour'

select  sum(BLOCKS*BLOCK_SIZE) / 1024 / 1024 arch,
        to_char(completion_time,'YYYY/MM/DD') jour
from v$archived_log
where completion_time >= sysdate - 8
group by to_char(completion_time,'YYYY/MM/DD');

archive log list;