|
Your answer really depends on the database version and
how you set up the tablespaces. What you describe is
called "fragmentation of free space". This is a
problem as you are noticing. You have tons of free
space, but each piece of free space is so small as to
make it unusable.
Since this is a problem for you, I'm assuming that you
are using a Dictionary Managed Tablespace (DMT). Prior
to Oracle 8i, DMT's were the only type of tablespace.
In a DMT, SMON is responsible for coallescing free
space, but it won't move objects around to unfragment
free space. And there is no single command which will
do this either.
Oracle 8i introduced the Locally Managed Tablespace
(LMT). Most DBAs now use LMTs with a uniform extent
size. This way, all free space chunks always equal the
segment's extent size. You will never have fragmented
free space that becomes unusable. All free space
fragments always equal any extent size, therefore it
is always usable.
To fix your problem, I would create a secondary
tablespace and move all tables to this tablespace with
the ALTER TABLE MOVE command. Indexes can be moved
with the ALTER INDEX REBUILD. Then, drop your
tablespace and recreate it. If you are using Oracle
8i+, then make sure that this is a LMT with uniform
extent sizes. This can be done as follows:
CREATE TABLESPACE new_ts
DATAFILE '/somedir/new_ts01.dbf' SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;
Now move everything back into this tablespace and drop
your secondary tablespace.
If you are using pre-Oracle 8i, then you cannot use a
LMT. But you can still unfragment your free space. In
this case, the ALTER TABLE MOVE command is not
available to you. So you'll have to export your
objects first, and then import them back.
For More Information
|
