RE: dbms_space.free_blocks does not report correctly ?

From: Brian MacLean <bmaclean_at_vcommerce.com>
Date: Fri, 20 Apr 2001 15:32:54 -0700
Message-ID: <F001.002EE666.20010420153529@fatcity.com>

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. <SPAN
class=559582222-20042001> 
<SPAN
class=559582222-20042001>Lastly, I just can't see spending time looking for a few blocks.  1000's maybe.
<SPAN
class=559582222-20042001> 
That's
just my opinion, I could be wrong.

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Diego Cutrone   [mailto:dcutrone_at_afip.gov.ar]Sent: Friday, April 20, 2001 1:46   PMTo: Multiple recipients of list ORACLE-LSubject:   dbms_space.free_blocks does not report correctly ?   Hi list,
 

  I'm checking space usage in some
  objects.
 

  <FONT face=Arial

  size=2> Type            
  Name                          

               
    TBlocks   Unused    

  Used  FBlocks      HWM
  <FONT face=Arial
  size=2>TABLE          
  FND_CONCURRENT_REQUESTS     
  21,075       70     
  3,557   20,225       21,005

  <FONT face=Arial
  size=2>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
 
 
 
 
Received on Fri Apr 20 2001 - 17:32:54 CDT