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 Fri Oct 17 1997 - 00:00:00 CDT
