Re: PCTFREE & PCTUSED

tilwenbr_at_netscape.net wrote:

> Ok, I have a question about PCTFREE & PCTUSED.
>
> First we are on 8.1.7.4 on VMS.
>
> When these parameters are changed do they affect immediately?

Yes and no.

That is to say, yes, the new setting applies immediately to the entire table. But consider what happens when you change, say, PCTFREE from 10 to 20. You already have blocks which are already 90% full, because of the previous setting of PCTFREE. Oracle won't go an re-distribute their rows so that they are modified to be only 80% full after the change.

So really, only new blocks that get DML directed at them will notice the change. However, a block which is currently only 60% full will indeed stop at the new 80% full mark, so in that sense the new setting is 'immediately' picked up. The difference is between blocks currently in use, and blocks which are no longer currently in use because they've already hit the previous PCTFREE setting.

Which is what you go on to say your understanding is, and you are therefore correct in that understanding.

Regards
HJR
> According to the concepts guide it states this:
> When altering the data block space usage parameters (PCTFREE and
> PCTUSED) of a table, note that new settings apply to all data blocks
> used by the table, including blocks already allocated and subsequently
> allocated for the table. However, the blocks already allocated for the
> table are not immediately reorganized when space usage parameters are
> altered, but as necessary after the change. The data block storage
> parameters are described in "Managing Space in Data Blocks".
>
> I interpret this as that we do not have to reorganize the table for
> these settings to take affect on the blocks already allocated;
> however, they do not take immediate affect until "later" or the block
> reaches the new "threshold" of these settings.
>
> Am I reading this correctly?
>
>
> BTW, this is a vendor db, so we can not go to DM tablespaces yet. :(.
>
> Mike
Received on Mon Oct 04 2004 - 15:54:41 CDT