Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

have FREELISTS=20.

  • FREELIST GROUPS should be set to the number of Oracle Parallel Server instances that access the table.  For partitioned objects and cases of segment header contention, freelist_groups my be set for non-RAC systems.

The PCTFREE parameter is used to reserve space on each data block for the future expansion of row values (via the SQL UPDATE command). Table columns may be defined as allowing null values that do not consume any space within the row, or with varchar data types. A varchar data type specifies the maximum allowable length for the column instance, but the acceptable range of values may be anywhere from 4 bytes (the size of the length holder) to the size of the field plus 4 bytes. Hence, a varchar(2000) may range in size from 4 bytes to 2004 bytes.

If an application initially stores rows with empty values and later fills in the values, the PCTFREE parameter can dramatically reduce I/O contention. If a  block of storage is filled by the addition of a row, subsequent updates to that row to fill in column values will cause the row to fragment--usually onto the next available contiguous block.

Next letís cover the main tablespace types within Oracle and show you how an up-front tablespace design decision can made a huge difference after your system is implemented.

The issue of PCTFREE

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

The setting for PCTFREE is especially important when a row is initially stored small and expanded at a later time. In such systems, itís not uncommon to set PCTFREE equal to 95, telling Oracle to reserve 95 percent of the data block space for subsequent row expansion.

Sadly, Oracle9i doesnít allow you to specify the value for PCTFREE if youíre using automatic space management. This is a serious limitation because Oracle9i canít know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance.

To see this problem, letís start by creating a 2-KB tablespace using automatic space management:

create tablespace

We can create a table in this tablespace with an unexpanded VARCHAR2(2000) datatype by entering the following commands. Later, weíll expand the rows and see if there is fragmentation.

create table
   tab_key    number,
   big_column varchar2(2000)

Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.