select d.type
       , trunc(completion_time) as "DATE"
       , trunc(sum(blocks*block_size)/1024/1024/1024) as GB
       , count(1) cnt
from   v$archived_log l 
join   v$archive_dest_status d on d.DEST_ID = l.DEST_ID and d.status='VALID'
where  first_time > trunc(sysdate-10)
group by d.type, trunc(completion_time)
order by 2 desc ,1;

alter session set nls_date_format='DD MON YYYY';

select  thread#, 
        trunc(completion_time) as "date", 
        to_char(completion_time,'Dy') as "Day", 
        count(1) as "total",
        sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00",
        sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01",
        sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02",
        sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03",
        sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04",
        sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05",
        sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06",
        sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07",
        sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08",
        sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09",
        sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10",
        sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11",
        sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12",
        sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13",
        sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14",
        sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15",
        sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16",
        sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17",
        sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18",
        sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19",
        sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20",
        sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21",
        sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22",
        sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23"
from    v$archived_log
where   first_time > trunc(sysdate-10)
and     dest_id = (select dest_id 
                   from   v$archive_dest_status 
                   where  status='VALID' 
                   and    type='LOCAL')
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') 
order by 2,1;