歸檔日誌大小統計

我們可能經常需要評估每天或每小時產生的歸檔日誌量，那麼怎樣計算出大小呢？
So How can we calculate archivelog size each day/hour？
主要還是查詢檢視V$ARCHIVED_LOG，分享一下這幾個SQL:
1，Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

2，Archivelog size each hour:
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';

Session altered.

SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;

3， another example，
SQL> SELECT  to_char(completion_time,'YYYYMMDD')    run_date,  Round(Sum(blocks * block_size + block_size) / 1024 / 1024 / 1024) redo_blocks
FROM  v$archived_log GROUP BY To_char(completion_time,'YYYYMMDD') ORDER BY 2;

這裡註釋：
V$ARCHIVED_LOG contains BLOCKS ( Size of the archived log (in blocks) ) and BLOCK_SIZE ( which is the same as the logical block size of the online log from which the archived log was copied )