Remove fragmentation of tables and indexes in Oracle

How to remove fragmentation of tables and indexes in Oracle when tablespaces are in LMT and DMT?

I am working as an Oracle DBA at MNC in India. My question is how to remove fragmentation of tables and indexes in Oracle when tablespaces are in LMT and DMT.

The best way to minimize tablespace fragmentation is to use Locally Managed Uniform Sized Tablespaces. With Oracle...

10g, even your system tablespace can now be LMT. If you are not using Locally Managed Uniformed Sized Tablespaces, I highly recommend you start. This alone can go a long way to minimizing fragmentation.

That being said, what do you do if you have fragmented tables and indexes? The best way to remove fragmentation of a table or index is to re-create or rebuild it. The best way to accomplish this is to use the ALTER TABLE MOVE and ALTER INDEX REBUILD statements. If you are using DMT tablespaces, then first create new LMT tablespace with a uniformed size. Then use the following to move your tables and indexes to it.

  Alter Table Tab1 move tablespace NEW_LMT_TS;
  Alter Index Ind1 rebuild tablespace NEW_LMT_TS;

Even with LMT, indexes can sometimes become fragmented over time due to inserts and deletes of rows in the index leaf pages. I have found the following useful to identify fragmented indexes.

  1. Run an analyze index validate structure on your indexes.
  2. Query the index_stats table. If the percentage of deleted leaf rows to total leaf rows (DEL_LF_ROWS/LF_ROWS) is greater than 20%; then, the index probably should be rebuilt.
Hope this helps.

Dig Deeper on Oracle database design and architecture