Informa por horário quantidade de switch gerado pelo redo

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999
SELECT
    to_char(first_time,'YYYY-MON-DD') day,
    to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
    to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
    to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
    to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
    to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
    to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
    to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
    to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
    to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
    to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
    to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
    to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
    to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
    to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
    to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
    to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
    to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
    to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
    to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
    to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
    to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
    to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
    to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
    to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
    v$log_history
    GROUP by
to_char(first_time,'YYYY-MON-DD') order by day;

Informa por horário quantidade de switch gerado de redo e o tamanho em MB (Por Hora)

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives
from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#
order by 1;

Informa a quantidade de switch/mb por DIA

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated
from v$archived_log
where COMPLETION_TIME > sysdate-10
group by trunc(COMPLETION_TIME,'DD'),
thread# order by 1,2;

Informa quem gerou mais redo

SELECT s.sid,
       sn.SERIAL#, 
       sn.username, 
       n.name, 
       ROUND (VALUE / 1024 / 1024, 2) redo_mb, 
       sn.status, 
       sn.TYPE 
       FROM v$sesstat s 
       JOIN v$statname n ON n.statistic# = s.statistic# 
       JOIN v$session sn ON sn.sid = s.sid 
       WHERE n.name LIKE 'redo size' AND s.VALUE <> 0 
       ORDER BY redo_mb DESC;

Obs: Com estas informaçoes é possivel fazer o cruzamento de dia/hora/user

Informa a quantidade corrente do archived_log e a Usada.

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999e
col pct_used format 999
select name, floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used/ 1024 / 1024) "Used MB"
from v$recovery_file_dest;