RE: dbms_space.free_blocks does not report correctly ?

Frankly, I am guessing, but all I could find in the documentation is that user must have analyze privilege for running dbms_space package, so my guess is it uses "estimate statistics" (sampling ) internally. Hence, the inaccuracy?

-----Original Message-----
Sent: Friday, April 20, 2001 4:46 PM
To: Multiple recipients of list ORACLE-L

Hi list,  

I'm checking space usage in some objects.  

 Type            Name                                              TBlocks
Unused     Used  FBlocks      HWM 

TABLE           FND_CONCURRENT_REQUESTS      21,075       70        3,557
20,225       21,005 
TABLE           GL_BALANCES                               126,233      220
125,987        4     126,013 
 
 

I've used the following query to calculate Used blocks (I mean blocks where there're rows, at least one).
SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM segment;
---> "Used"
 

And I've used dbms_space.free_blocks to calculate "FBlocks". And dbms_space.unused_blocks to calculate "Unused" (blocks above HWM), "TBlocks" (Total blocks of the object).
And HWM=TBlocks-Unused  

So, check this out  

Table GL_BALANCES, I've got 126233 blocks and 220 blocks unused, so we can say that HWM is 126013 (126233-220).
And I've got 125987 blocks with some data in them, so 126013-125987= 26 blocks. This means that these 26 blocks were used some time ago (because they are below HWM) but they're not holding any rows now. right?
So here's my question:

    why does dbms_space.free_blocks report only 4 blocks ?????  

    that means that below HWM there are 4 blocks that are candidate for inserts, what happened with the 26 blocks !??

    shouldn't it be reporting at least 26 blocks ?    

Please help me out with this.
Thanks        

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Trivedi, Hitarth
  INET: HTrivedi_at_telergy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).