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');
