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

Reply via email to