Reclaiming Oracle disk space
Oracle Database Tips by Donald Burleson
My tablespace is fragmented and I
want to reduce the size of my dbf files to free up disk.
How do I reclaim disk space in Oracle?
In a nutshell, the best way to reclaim disk space and ensure
tightly-packed rows, is to export (or copy) the tables,
adjusting the storage parameters to tightly pack them onto the
data blocks and to ensure that subsequent update activity does
not re-fragment the data.
I've been reclaiming
disk space since the 1980's when disk was $200,000 per meg!
Oracle is designed for high
performance, and Oracle does not clean up as rows are added and
deleted, thus leaving a honeycomb of free space within the
physical data blocks.
As such, there are
lots of opportunities for using scheduled downtime to reclaim
free disk space, and reclaiming disk space is a part of the job
of an Oracle DBA.
There are several
considerations on reclaiming disk space in Oracle:
- Free blocks
within tables: Oracle may have significant free space
on the freelists, mostly empty blocks waiting to be used by
a table or index.
Oracle maps the physical files (customer.dbf)
to their logical "tablespace" construct and Oracle places
objects (tables & indexes) into the tablespace.
Within the tablespace, objects are
scattered throughout the tablespace and corresponding datafiles.
There are several types of fragmentation, most common the
"honeycomb" fragmentation and the "bubble" fragmentation, where
it is more difficult to reclaim wasted disk space. Note
that you can remove bubble fragmentation by using
locally-managed tablespaces, and see these notes on
Oracle data file fragmentation.
Tables, indexes and tablespaces
will naturally fragment as a function of update activity and
Oracle has many methods for reclaiming disk space and a segment
advisor which will recommend when tables and indexes will
benefit from a reorganization to free up disk space.
Oracle has several tools to help
reclaim disk space:
Oracle leaves the high-water mark alone after
rows are deleted, and you can reclaim space at the table level
with these techniques, all of which lower the high water mark
for the table, thereby freeing-up the space:
- For a complete reorganization and space reclamation,
export/import allows you to restructure your files and
reclaim lost space.
dbms_redefinition - This procedure will reorganize a
table while it remains online for updates.
table xxx shrink - If
you were 10g and beyond you could use "alter table xxx
shrink space compact;" syntax.
- You can look at the
coalesce table syntax.
Unlike the "deallocate unused space" syntax which removes space
above the high-water mark, "coalesce" puts together
discontiguous fragmented extents. There are two type of
space fragmentation in Oracle.
First is the honeycomb
fragmentation, when the free extents are side by side, and the
"Swiss Cheese" fragmentation, when the extents are separated by
alter table xxx coalesce;
You can also deallocate unused space. Oracle
notes that the "deallocate unused space" clause is used to to
explicitly deallocate unused space at "the end" of a segment and
makes that space available for other segments within the
alter table xxx deallocate unused space;
alter index xxx deallocate unused space;
deallocates unused space beginning from the end of the objects
(allocated space) and moving downwards toward the beginning of
the object, continuing down until it reaches the high water mark
(HWM). For indexes, "deallocate unused space" coalesces
all leaf blocks within same branch of b-tree, and quickly frees
up index leaf blocks for use.
Regardless of your
approach for reclaiming disk space, you will need to run
complex dictionary scripts
to detect the areas of fragmentation, and these scripts can also
be used to reclaim the space wastage.