oracle archivelog usage
WITH hours AS (
SELECT trunc(sysdate) + ((LEVEL - 1) / 24) AS HOURS
FROM DUAL
CONNECT BY LEVEL < = 24
)
SELECT h.HOURS AS DATE_HOUR,
round(sum(BLOCKS * BLOCK_SIZE) / 1e9, 2) SIZE_GB
FROM hours h
LEFT OUTER JOIN V$ARCHIVED_LOG al ON h.HOURS = trunc(al.FIRST_TIME, 'HH24')
GROUP BY h.HOURS
ORDER BY h.HOURS;