by Claus Jense » Sat, 18 Oct 1997 04:00:00
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.
by Dick Alli » Sat, 18 Oct 1997 04:00:00
> 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.
by Michael Serbanesc » Sun, 19 Oct 1997 04:00:00
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
the script $ORACLE_HOME/rdbms/admin/dbmsutil.sql.
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.
by R Apont » Tue, 21 Oct 1997 04:00:00
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
by Kluge » Thu, 23 Oct 1997 04:00:00
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
1. New Extent Allocation of Segments
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.
3. extent in the wrong segment
4. Approach front-end, Linux behind?
5. Gemstone support for PP and ST/V
6. Error 2558: Extent Not Within Segment
8. Extent not within segment error
9. Extent not within segment on system tables !!!
10. ORA-01658: unable to create INITIAL extent for segment in tablespace
11. Unable to create INITIAL extent for segment in tablespace