Tuesday, February 25, 2014

What is PCTFREE and PCTUSED in Oracle Block.


   PCTFREE and PCTUSED are the block storage parameters.


1. PCTFREE- This parameter is used to specify how much space should be left in the block for updates.
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. U should 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 : As I have mentioned above, Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit.
Now consider this ---- When should Oracle start inserting new rows in the Block ?
PctUSED parameter is taken into consideration for the answer of the above question.
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.

No comments:

Post a Comment