I am trying to track the data growth for specific objects(object names with '%2MP%') in ORACLE 11G database(Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production) for the last 60 days using the below sql:
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, segment_name,SEGMENT_TYPE, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
where begin_interval_time > trunc(sysdate) - 60
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name like '%2MP%'
and C.owner = 'DITMGR'
group by to_char(end_interval_time, 'MM/DD/YY'), segment_name, SEGMENT_TYPE)
order by segment_name,SEGMENT_TYPE, to_date(mydate, 'MM/DD/YY');
Some objects(which are present in DB) are missing from my sql output. I thought those objects were not analyzed recently but statistics were gathered for the whole database yesterday night. Join condition between dba_hist_seg_Stat.obj# = dba_objects.object_id is failing for many objects. Please let me know what am I missing here?