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

Comments

comments

Tags: Archive, Archive Log, Archive Log Mode, Check Oracle Archived, Check Oracle Archived Redo Log, Check Oracle Archiving, Check Oracle Redo Log, How to disable Archive Log Mode, How to Enable Archive Log, Log Mode, Oracle, Oracle Archived Redo, Oracle Archiving, Oracle Archiving Log, Oracle Database 10g, Oracle Database 11g, Oracle DBA, Oracle DBA Task, Redo Lob, Redo Log, Steps To Configure Archive Log Mode in Oracle Database 10g and 11g, Steps To Configure Archive Log Mode in Oracle Database 11g

This entry was posted on Saturday, November 25th, 2017 at 12:01 am and is filed under Oracle Queries. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.