How to check archived redo volume by hour in Oracle

Bucket archived redo by hour for a specific day: prompt with 0 for today, 1 for yesterday, and so on. Complements the daily view when you need to see exactly when the redo burst happened.

SET VERIFY OFF PAGESIZE 30

ACCEPT p_day_offset NUMBER PROMPT 'Day offset (0=today, 1=yesterday, ...): '

WITH hours AS (
  SELECT TRUNC(SYSDATE) - &&p_day_offset + ((level - 1) / 24) AS hours
  FROM   dual
  CONNECT BY level <= 24
)
SELECT h.hours AS date_hour,
       ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS size_gb
FROM   hours h
LEFT JOIN v$archived_log al ON h.hours = TRUNC(al.first_time, 'HH24')
GROUP BY h.hours
ORDER BY h.hours;

SET VERIFY ON PAGESIZE 14