Cry about...
Oracle How To ...

---

Reducing database fragmentation

---

Excessively fragmented tables or indexes can adversely affect performance. Use the following SQL to identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;

In general, if a table or index has more than 10 extents then rebuild it to fit into one extent.

A table can only be rebuilt by exporting and then importing it. The database will be unavailable for use by applications during this time. The steps to accomplish this are:

1. Export the table with COMPRESS=Y
2. Drop the table
3. Import the table.

An index can be rebuilt without preventing others from still using it. Firstly change the storage parameters to make the ‘next’ storage parameter larger (perhaps double it). The initial storage value cannot be changed. Then rebuild the index.

---

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.