Managing DB Objects   «Prev  Next»
Lesson 12 Reducing Oracle fragmentation
Objective 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.
The ALTER TABLESPACE command can be called with the keyword COALESCE. The syntax for this command is:
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.

Coalesce Tablespace

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.