Re: table size not true with dba_segments??

On 27 Mar, 13:31, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Mar 27, 12:48 pm, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
>
>
> > hi,
> > i've a oracle 9i db and after run dbms_stats i find a table size with
> > this command:
>
> > select bytes,initial_extent,next_extent,extents from dba_segments
> > where segment_name='TRANSIT' and OWNER='SACT';
>
> > the ouput is this:
>
> > BYTES INITIAL_EXTENT NEXT_EXTENT EXTENTS
> > ---------- --------------
> > ----------- ----------
> > 756678656 67502080 377524224 7
>
> > so, there is something wrong :-?
>
> > if next_extent is about 377Mb and the extents allocated is 7, it
> > should be more then 756Mb (BYTES). Like 67Mb + 377Mb *6 , (initial +
> > 6 extents)
> > Instead, why i've gather this result?
>
> > how i estimate table size?
>
> > thanks a lot
>
> > andrew
>
> Nothing wrong, apart from the fact you should realize (or look up in
> the documentation) the size of the next_extent is the size which will
> actually be allocated, when the next_extent is created. So next extent
> in dba_segments = the result of next_extent in dba_tables +
> pctincrease in dba_tables. If you look in dba_extents, you will see
> bytes in dba_extents adds up to bytes in dba_segments (it better
> would, as dba_segments is based on dba_extents).
> So apart from the usual not reading the documentation and jumping to
> usenet right away, there is nothing wrong.
>

ok, sorry.

select initial_extent,next_extent,PCT_INCREASE from dba_tables where table_name='TRANSIT';

INITIAL_EXTENT    NEXT_EXTENT     PCT_INCREASE
--------------              -----------                ------------
67502080              377524224           50

select EXTENT_ID,bytes,blocks from dba_extents where segment_name = 'TRANSIT';

EXTENT_ID       BYTES        BLOCKS
 ----------             ----------         ----------
         4            111935488     27328
         5            167903232     40992
         6            251789312     61472
         3            74579968       18208
         0            67502080       16480
         1            33161216       8096
         2            49807360      12160

so, PCT_INCREASE is 50 , therefore every extend is increased about 50%.

But if i understand, this method don't estimate the really size of a table because the last extent allocated contained also free space. Therefore, how i find the actual really size of a table??

i know, i'm very wrong DBA :( Received on Tue Mar 27 2007 - 08:43:35 CDT