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.

    Requires Free Membership to View

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.

This was first published in May 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: