Re: Space Remaining in Current Extent

Kevin,

    Unfortunately, there is not a way to find out the space available in an extent, without doing block dumps. Why is this the only 'accurate' method?

1. Using rowid will not work because it only records the rowid of the head piece of a chained/continued row. This makes blocks appear to have space available, when they are in fact being used. It also makes other blocks appear full when they have very few full rows in them.
2. Oracle does not track space allocation at the extent level, but rather at the object (table/index) level.

    If you want to get a close (low confidence) estimate, use the difference between the sum of the blocks allocated to the segment and the value of num_blocks in dba_tables/leaf_blocks in dba_indexes. The leaf_blocks in indexes is not complete, but I am not sure about determining the number of branch blocks to be included in the calculation. One of the problems with this approach is if the high water mark is set artificially high or the table has had a high amount of delete activity.

-- 
Daniel W. Fink
http://www.optimaldba.com


Kevin Lange wrote:



>Hey guys;

>  Does anyone know where I can look to find the space remaining in an

>individual extent ??  I know that you can get the freespace from

>dba_free_space but that seems to be based only on unallocated extents.  I

>have been asked to find out, down to the byte, how much free space is

>available ..... on the fly (which means not only after every analyze) 

>

>Any suggestions ??? I  am sure Oracle has a table with the information

>somewhere .

>

>Thanks

>

>Kevin

>  

>





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).