Re: Calculating when a segment will need a new extent

check out the dba_segments table.

Dick Allie wrote:
>
> Claus Jensen wrote:
> >
> > Hi,
> >
> > Is there a good (as in relatively accurate) way of finding out how much
> > room is left in the latest allocated extent for a table or index
> > segment? Or put another way is there a way of figuring out which
> > segments are close to needing an extra extent?
> >
> > This info could be used together with info on #extents, extent sizes and
> > free space to see where any space problems may occur soon.
> >
> > Thanks in advance for any help!
> >
> > Please email any answer to me at cje_at_stibo.com since I don't read news
> > regularly!
> >
> > Claus Jensen.
> Hi Claus,
> You can analyze the table with compute statistics phrase. Then look at
> the user_tables or all_tables or dba_tables for that table_name. It
> will show the current stats on the table as to the number of blocks
> used, number of blocks empty, number of rows, avg_row_length, etc.
> Through some arithmetic you can determine the avg number of rows per
> block the table uses. You should set up another stat table somewhere
> to record this info and revisit the same process at a regular interval.
> This way you can calculate the growth of the table to determine how much
> available space is left before the next extent is required.
> Similar information is available for indexes if you analyze index
> validate structure then query index_stats for info on number of rows in
> index, etc. Note - you have to query index_stats right after the
> analyze because only one row is held in that table.
> Look at descriptions of user_tables,user_tab_columns,user_indexes, and
> index_stats to see the info kept there. Some ORACLE tuning books
> go into more detail on determining space needed for tables and indexes.
> Hope this helps.
> Regards, Dick
  Received on Wed Oct 22 1997 - 00:00:00 CEST