Oracle PCTFREE and PCTUSED Parameters

PCTFREE and PCTUSED are the block storage parameters

PCTFREE- This parameter is used to specify how much space should be left in the block for updates.The default value is 10.

For Eg, if The PctFree for a block is 30, then Oracle keeps on adding new rows to the block until the block is 70 % full.
It leaves the 30% for future updates. That is, in case, any row is updated and requires some more space, it is taken
out from the 30% remaining in the Block.Specify the value of this parameter HIGH if future updates in the rows of
the table are going to need more space.In case ur table is less prone to updates, then this value can be specified LOW.



PCTUSED : Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit and removes it from the free list.
The data block is again added to the free list when the used space in it falls below the percent value set by the PCTUSED parameter.The default value is 40.
Once the data block is added to the free list, Oracle again starts inserting new rows into it.
Hence, if a large value is set for PCTUSED, the data blocks are more frequently returned to the free list and vice versa.

For Eg,Suppose u have specified PCTUSED as 40 %. and PCTFREE as 20 %.
1. Oracle will keep on inserting new rows till the space is 80 % used. It will reserve the remaining 20% for future updates.
2. To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.
3. When the space falls below 40 %, Oracle will start adding new rows to the block.