Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









PCTFREE vs. minimize_records_per_block

Don Burleson


Many Oracle systems store rows as small and then expand them with SQL UPDATE statements causing the rows to fragment into multiple blocks.

To prevent the inevitable row migration that results from extreme row size expansion, I set PCTFREE to a high value whenever rows are stored small, and later expanded.

For example, I had a lab system that stored row "stubs" at 40-bytes and then updated a VARCHAR2 column, causing the avg_row_len to be over 500 bytes and the value of chain_cnt to become huge. During the table reorganization, I re-set PCTFREE to un-link the block from the freelist after only 200 bytes was stored. That way, the rows could expand without migrating to other blocks.

But there is another method, using a "minimize records_per_block" feature.  With this approach you do these steps:

1 - Find-out the number of rows you want to have in a block at full expansion, say 5 rows/block.

2 - You insert that number of records in that table (e.g 5)

3 - Then issue "alter table xxx minimize_records_per_block;"

4 - Then, delete those 5 rows and then load your table

You will then only get that many rows in a block and your rows can then grow without migrating.  This approach is useful if you know exactly how big your rows are going to be.

However, the Oracle documentation does not talk about minimize_records_per_block usage for this purpose::

Use SQL statements with the ALTER TABLE syntax to optimize the mapping of bitmaps to rowids. The MINIMIZE RECORDS_PER_BLOCK clause enables this optimization and the NOMINIMIZE RECORDS_PER_BLOCK clause disables it.

When enabled, Oracle scans the table and determines the maximum number of records in any block and restricts this table to this maximum number. This enables bitmap indexes to allocate fewer bits per block and results in smaller bitmap indexes. The block and record allocation restrictions this statement places on the table are only beneficial to bitmap indexes.

Therefore, Oracle does not recommend using this mapping on tables that are not heavily indexed with bitmap indexes.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.