|
|
| |
 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Oracle
Tablespace Fragmentation
As rows are added to tables, the table expands into unused space
within the space. Conversely, when rows are deleted, a table
may coalesce extents, releasing unused space back into the
tablespace. As this happens, it is possible for there to be
discontiguous chunks, or fragments of unused space within the
tablespace. Whenever the value for a table as specified by STORAGE
(INITIAL xx) is exceeded, Oracle will create a new extent for the
table. If the PCTINCREASE is set to 0, a new extent of the size
specified in STORAGE (NEXT xx) will be added to the table. If
PCTINCREASE is non-zero, the extent size will be equal to the value
of the most recent extent size multiplied by PCTINCREASE.
Note: PCTINCREASE for a tablespace should never be set to zero,
since this will disable the automatic coalesce facility for Oracle
tablespaces. In general, all tablespaces except the system
tablespaces (SYSTEM, RBS) should have PCTINCREASE set to 1. The
PCTINCREASE parameter for tablespaces is generally only used when a
table is allocated without a STORAGE clause--although Oracle also
uses it for coalescing.
This allocation of new extents will be physically contiguous to the
table’s initial location, as long as the next physical data blocks
are empty. Unfortunately, as many tables populate a tablespace, a
table may not have contiguous data blocks for its next extent, which
means that it must fragment the extents onto another spot in the
datafile, as shown here:
CREATE TABLESPACE SALES
DATAFILE '/Data/ORACLE/sales/sales.dbf'
SIZE 500M REUSE
DEFAULT STORAGE (INITIAL 500K NEXT 50K PCTINCREASE 1);
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
