데이타의 급격한 증가 시 확인 방법 FROM AWR

데이타의 급격한 증가 시 확인 방법 FROM AWR 
출처 : How To Get Table Growth History Information? (문서 ID 1395195.1)

 

<< 참고 >>

2018.11.05 - [Oracle/Oracle Script] - << Datafile Resize Script >>

 

-- 기잔별 증가가 높은 SEGMENT 뽑기 
select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'YYYY/MM/DD') start_day,
         ROUND(sum(a.SPACE_USED_DELTA)/1024/1024) block_increase_MB 
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('2022/12/06','YYYY/MM/DD')  and to_timestamp('2022/12/07','YYYY/MM/DD')
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'YYYY/MM/DD')
ORDER BY block_increase_MB DESC  
/
-- 특정 OBJECT 증가량 뽑기 ( SCOTT.EMP ) 
select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'YYYY/MM/DD') start_day,
         ROUND(sum(a.SPACE_USED_DELTA)/1024/1024) block_increase_MB 
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('2022/12/06','YYYY/MM/DD')  and to_timestamp('2022/12/06','YYYY/MM/DD') + 1 
AND      obj.owner ='SCOTT'
AND      obj.object_name ='EMP' 
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'YYYY/MM/DD')
-- order by obj.owner, obj.object_name
ORDER BY start_day 
/