Script to get size of generated archived logfiles in certain timeframe

I’m testing a import datapump load procedure and I need to know the approximate size of generated archives that this load will generate to determine the size needed for our database recoveryarea.

Script below queries the v$archived_log view for this information.

Please note that I have tested this with ONE and only ONE active / valid log_archive_dest destination. I THINK that when there is more than one active log_archive_dest active these archived log entries will be written to v$archived_log as well and the query will not ‘work’ anymore.

SQL> SELECT dest_id, status, dest_name, destination
  2    FROM v$archive_dest_status
  3   WHERE status <> 'INACTIVE'
  4  /
---------- --------- ------------------ --------------------------------------------------
         2 VALID     LOG_ARCHIVE_DEST_2 /ora/recoveryarea/overflow/ 
col archives_m for 999,999,999,999,999

select round ( sum ( blocks * block_size ) /1024 /1024 )  archives_m
  from v$archived_log
 where first_time > to_date( '2013-AUG-30-090000', 'YYYY-MON-DD-HH24MISS')
   and first_time < SYSDATE

SQL> select round ( sum ( blocks * block_size ) /1024 /1024 )  archives_m
  2    from v$archived_log
  3   where first_time > to_date( '2013-AUG-30-090000', 'YYYY-MON-DD-HH24MISS')
  4     and first_time < SYSDATE
  5  /


OK – I can report back to project the approximate size needed for our recoveryarea to ‘survive’ our new load interface.

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>