We help IT Professionals succeed at work.
Get Started

Table growth - Mismatch b\w dba_objects and dba_hist_seg_Stat

sivi_3883
sivi_3883 asked
on
4,134 Views
Last Modified: 2013-12-18
Hello All,

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 11.1.0.7.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"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
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');

Issue:

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?

Regards
Siva
Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE