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’;



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.