Site icon An Oracle Spin by Alex Lima


PCTFREE – tells what percentage of space is reserved in each block of updates, if the value is too low and if for example of there is VARCHAR2 datatype and update is replacing the column with larger value it will cause the row not to fit in the block causing row fragmentation which causes unnecessary I/O when the row is retrieved. So for tables that doesn’t change i.e. static data one can set PCTFREE to a very low value, and for tables in which only inserts occur one can set the value to be 0 to maximize the space in the block.

One can find the value of PCTFREE by running by querying user_tables or all_tables.
SELECT pct_free FROM user_tables WHERE table_name = ‘EMP’;


Exit mobile version