RE: dbms_space.free_blocks does not report correctly ?

From: Diego Cutrone <dcutrone_at_afip.gov.ar>
Date: Mon, 23 Apr 2001 10:18:36 -0700
Message-ID: <F001.002EF521.20010423102056@fatcity.com>

Thanks Trivedi and Brian,
I think that you may be right about chained blocks, I'll check this out.
 
<BLOCKQUOTE dir=ltr
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Brian MacLean To: <A href="mailto:'ORACLE-L_at_fatcity.com'" title=ORACLE-L_at_fatcity.com>'ORACLE-L_at_fatcity.com' ; <A href="mailto:dcutrone_at_afip.gov.ar" title=dcutrone_at_afip.gov.ar>'Diego Cutrone' Sent: Friday, April 20, 2001 4:34 PM Subject: RE: dbms_space.free_blocks does not report correctly ?

  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> 
  <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
 
 
 

    <FONT face=Arial
size=2>  Received on Mon Apr 23 2001 - 12:18:36 CDT