アーカイブログ サイズ確認SQL

・アーカイブログ出力済サイズ確認用SQL

・全体確認
STATUS列で使用可能（=A)の物に絞り込んでいるので削除確認時等には該当箇所をコメントアウトする

set line 250
set pages 1000
col name for a60
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select
 a.name
 ,a.RECID
 ,a.THREAD#
 ,a.SEQUENCE#
 ,trunc(((a.BLOCKS * a.BLOCK_SIZE)/1024/1024),1) as "arc_size(MB)"
 ,a.COMPLETION_TIME
from v$archived_log a
where a.STATUS ='A'
order by a.SEQUENCE#;

・日別確認

set line 250
set pages 1000
alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
select
 trunc((a.COMPLETION_TIME),'DD') as arc_date
 ,count(*) as arc_count
 ,sum(trunc(((a.BLOCKS * a.BLOCK_SIZE)/1024/1024),1)) as "arc_size(MB)"
from v$archived_log a
where a.STATUS ='A'
group by trunc((a.COMPLETION_TIME),'DD')
order by 1 ;

・時間別確認

set line 250
set pages 1000
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select
 trunc((a.COMPLETION_TIME),'HH') as arc_date
 ,count(*) as arc_count
 ,sum(trunc(((a.BLOCKS * a.BLOCK_SIZE)/1024/1024),1)) as "arc_size(MB)"
 ,sum(trunc(((a.BLOCKS * a.BLOCK_SIZE)/1024/1024/1024),1)) as "arc_size(GB)"
from v$archived_log a
where 1=1
and a.DEST_ID=1
group by trunc((a.COMPLETION_TIME),'HH')
order by 1;

・日別最大サイズ確認

set line 250
set pages 1000
alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
select max(arc_size)
from
(select
 trunc((a.COMPLETION_TIME),'DD') as arc_date
 ,count(*) as arc_count
 ,sum(trunc(((a.BLOCKS * a.BLOCK_SIZE)/1024/1024),1)) as arc_size
from v$archived_log a
where a.DEST_ID = 1
group by trunc((a.COMPLETION_TIME),'DD')
);