Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?

RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?

From: <>
Date: Wed, 25 Sep 2002 08:33:22 -0800
Message-ID: <>

Hemant wrote
Therefore, to reduce the contention for the "hot blocks", I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ?
How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ?

I would guess the following Hemant,
After changing the PCTFREE/PCTUSED values I do not think existing blocks will be changed until sufficient rows are deleted so that a block will be available on the freelist.
If as you say there is only 1 row to a block then that would need to be deleted so that the block became available on the freelist and the new values would apply once a new row was inserted. Moving on from there it seems logical to me (that may be where I am going wrong!!) that an existing empty block will have it's values changed and will remain on the freelist but with revised parameters. I cannot say for certain without performing some tests but that is my gut feel

HTH John

Please see the official ORACLE-L FAQ:

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Sep 25 2002 - 11:33:22 CDT

Original text of this message