특정 테이블스페이스에서 오브젝트 용량 증가량 조회

DBA_HIST_SEG_STAT 테이블을 기반으로 만든 쿼리입니다.

전,후의 스냅샷의 정보를 각각 조회하여 마이너스 해서 증가량을 구했습니다.

 

select bg.ts_name,bg.obj_name,bg.begin_interval_time as begin_snap_time,ed.begin_interval_time as end_snap_time, round((ed.space_used_total - bg.space_used_total)/1024/1024,2) as object_usage_growth_MB
from
(
select  /*begin_seg_space*/
a.snap_id,b.begin_interval_time,d.name as ts_name,c.name as obj_name,a.space_used_total,a.space_used_delta
from DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b, sys.obj$ c ,sys.ts$ d
where  a.snap_id = b.snap_id
and a.obj# = c.obj#
and a.ts#=d.ts#
and d.name=upper(:ts_name)
and a.snap_id = (select min(snap_id) from DBA_HIST_SNAPSHOT where begin_interval_time >= :begin_time) 
) bg,
(
select  /*end_seg_space*/
a.snap_id,b.begin_interval_time,d.name as ts_name,c.name as obj_name,a.space_used_total,a.space_used_delta
from DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b, sys.obj$ c ,sys.ts$ d
where  a.snap_id = b.snap_id
and a.obj# = c.obj#
and a.ts#=d.ts#
and d.name=upper(:ts_name)
and a.snap_id = (select max(snap_id) from DBA_HIST_SNAPSHOT where begin_interval_time <= :end_time)
) ed
where bg.obj_name=ed.obj_name
and bg.ts_name = ed.ts_name;

바인드변수

:ts_name	MY_DATA	String
:begin_time	2023/02/20 09:00:00	DATE
:end_time	2023/02/20 12:00:00	DATE​

결과값

TS_NAME                        OBJ_NAME                       BEGIN_SNAP_TIME                END_SNAP_TIME              OBJECT_USAGE_GROWTH_MB                       
------------------------------ ------------------------------ ------------------------------ -------------------------- -------------------------------------------- 
KYOBODATA9                     IFA_EAI_RESULT                 2023/02/20 09:00:34.907000     2023/02/20 11:00:32.622000                                      1986.86
KYOBODATA9                     DTM_YEARREST_EMP_BASE          2023/02/20 09:00:34.907000     2023/02/20 11:00:32.622000                                            0

2 rows selected.