Reducing Oracle fragmentation
Avoid/correct tablespace fragmentation in Oracle?
Correct Tablespace Fragmentation in Oracle
There are two ways to prevent the problems caused by tablespace fragmentation: you can correct the problem when it occurs or
avoid the problem through planning.
Correcting the problem
There is a single command you can use to eliminate the problem of fragmented tablespaces.
ALTER TABLESPACE command can be called with the keyword
COALESCE. The syntax for this command is:
ALTER TABLESPACE tablespace_name COALESCE;
where tablespace_name = the name of the tablespace
As the keyword implies, this command will
COALESCE all the extents within the tablespace. Any lost space due to
fragmentation is moved to the end of the tablespace. This command is complete in itself; you cannot have any additional
parameters if you use the
COALESCE keyword with the
ALTER TABLESPACE command.
The following SlideShow demonstrates how this process works.
Preventing the problem of Tablespace Fragmentation
You can prevent tablespace fragmentation by specifying all the extents within a tablespace to be the same size.
As you learned earlier, this standardization can be easily implemented by declaring storage parameters for the tablespace and not declaring storage parameters for the objects
within the tablespace.
This solution may seem like overkill to prevent an easily correctable problem. Sometimes it may be, since you may have good reasons,
unrelated to extent size, for placing different objects in different tablespaces.
Experienced researchers at Oracle have shown that there is not a great need for many different extent sizes.
Some experts recommend only three different extent sizes;
160K, 160 MB and 4 GB. With fewer extent sizes, it is much easier to assign objects to a tablespace, as many of them will have the same extent size.
The next lesson is the wrap-up for this module.