Database – Archive Log Sizes Spooled :

1) Info on archived logs spooled/deleted during the past 24 hours

2) Info on archived logs spooled/deleted during the previous 7 days

3) Info on archived logs spooled/deleted during the previous 31 days

1) Info on archived logs spooled/deleted during the past 24 hours:

SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-1
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

2) Info on archived logs spooled/deleted during the previous 7 days:

SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-7
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

3) Info on archived logs spooled/deleted during the past 31 days:

SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-31
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');