Re: difference in dba_tables and dba_segments??

Ben wrote:
> Running Enterprise 9.2.0.5 with the compatible parameter set to 8.1.0
> on an IBM Unix/AIX5L system.
> Why would the following selects return a different value for blocks?
> This table is analyzed using dbms_stats.gather_schema_stats.
>
> select owner, segment_name, round(blocks*8/1024,2) as Megs
> from dba_segments
> where owner = 'PRODDTA'
> and segment_name = 'F0911'
>
> returns 35840.02 for Megs
>
> select owner, table_name, round(blocks*8/1024,2) as Megs
> from dba_tables
> where owner = 'PRODDTA'
> and table_name = 'F0911'
>
> returns 35750.84 for Megs
>
> I'd rather use dba_table instead of joining between the two to get
> num_rows, last_analyzed, and size values, but if these are returning
> different values how do I know which one is correct?
> Thank you,
> Ben
>

In DBA_TABLES, the BLOCKS column is populated when you calculate statistics on the object. When you *estimate* stats, you may not get a truly accurate number.

In DBA_SEGMENTS, the BLOCKS column is the actual number of blocks allocated for that segment. I use this view for my true value.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown