Problem solve Get help with specific problems with your technologies, process and projects.

Defragmenting a tablespace to free up contiguous space?

I have a tablespace consisting of four data files. Each data file is 1 GB. Overall, the tablespace has around 1.5 GB of data free. But the maximum contiguous free space available is only 50 MB. Can I defragment a tablespace to solve this issue? If yes what is the command?

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:

DATAFILE '/somedir/new_ts01.dbf' SIZE 1000M
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

Dig Deeper on Oracle database design and architecture