Re: Database Reorg

> gmr_at_panix.com (George Reichert) writes:
> I've been asked to come up with criteria for database reorganizations. The
> database has become too fragmented. Does anyone have any such criteria?
> When do you reorg your databases?
>
> TIA,
>
> George
>
>>>>

One of the criteria could be number of extents used by a segment. If the max number goes above 10 (some such number) you may think it is defragmented.

You can come up with sevaral criteria (standard deviation, median, etc.) based on this:

select

     sum(extents) / count(*) extents_per_segments	/* ~ 1 desirable */
      max(extents)                                                      /* < 10 desirable */

select owner, segment_name, segment_type, sum(extents) from dba_extents
group by owner, segment_name, segment_type having sum(extents) > n /* try n = 5 */

To defragment, export tables, drop and create with appropriate storage clause and import again.

Ganesh Puranik,
Consultant, Surya Systems, Inc. Received on Mon May 22 1995 - 00:00:00 CEST