RE: dbms_space.free_blocks does not report correctly ?

[Brian MacLean]

I can't answer your question directly but I would like to point out a few things.  Your select count on rowid will miss chained blocks, the first block of the first segment which is used for freelist chains, and as I learned some time ago that oracle used more blocks to keep track of the extra freelists that became available with the "maxextents unlimited" option.  Remember years ago when we where bound by 121 extents for 2k block, and 225 for 4k, and 505 for 8k (something like that), anyway, oracle had to put the extra info somewhere, so that take extra blocks to store it all.   Lastly, I just can't see spending time looking for a few blocks.  1000's maybe.   That's just my opinion, I could be wrong. -----Original Message----- From: Diego Cutrone [mailto:[EMAIL PROTECTED]] Sent: Friday, April 20, 2001 1:46 PM To: Multiple recipients of list ORACLE-L Subject: dbms_space.free_blocks does not report correctly ? 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