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.
- Run an analyze index validate structure on your indexes.
- 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.
Dig Deeper on Oracle database design and architecture
Related Q&A from Phillip Bracken
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have a critical performance issue due to the large volume of data for a specific customer. Whenever customer XYZ's data is being fetched the query ... Continue Reading
I increased the RAM from 2GB to 3GB, but when I try to increase the sga_max_size I get the following error on startup: "ORA-27102: out of memory." Continue Reading