## Calculating when a segment will need a new extent

### Calculating when a segment will need a new extent

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!

regularly!

Claus Jensen.

### Calculating when a segment will need a new extent

> 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!

> 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

### Calculating when a segment will need a new extent

Use the DBMS_SPACE package provided by ORACLE. It contains two
procedures: UNUSED_SPACE and FREE_BLOCKS, that can provide the
information you need.

You can find details about the DBMS_SPACE package and its contents in

Hope this helps.

Michael Serbanescu
--------------------------------------------------------------------

> 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!

> regularly!

> Claus Jensen.

### Calculating when a segment will need a new extent

Get the book Oracle Performance Tunning from O'Reily publisher.  It comes
with a floppy and a lot of already made scripts to do what you need to do.
You can tailor them.

Becky Aponte

### Calculating when a segment will need a new extent

check out the dba_segments table.

> > 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!

> > 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

I want a method of determining when a segment such as a data table is
about to acquire a new extent because all existing extents are almost
full. This method should ideally involve querying the dictionary or
executing an Oracle-provided package, and could for example identify the
remaining number of average-sized records that can be inserted into the
table before another extent will be required.