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:
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.