||Oracle Tips by Burleson
Chapter 5 Oracle Tablespace
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
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
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:
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
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
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
Packed with more than 690 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.