Re: Tablespace Fragmentation

Nadira Gangadhar wrote:
> We're running Oracle 7.1.6 and 7.3.2 on HPUX and need some help with tablespace
> fragmentation problems. [....]
> How do we deal with this problem? I have been told that exporting, dropping
> the tablespace, and importing takes a considerable amount of time and is not
> feasible. We're currently evaluating a few tools that do reorgs (any
> suggestions here?), but it will be several months before we can get one.

        I suggest you examine the cause of your problem. Why do you have such a large incidence of fragmentation in your database? Do you frequently create
and drop tables and indexes of widely varying extent sizes?

        You might want to consider restructuring your database in some ways. What we do at my site is create a pair of tablespaces, INTERIM and INTERIM_IDX,
which are used strictly for tables and indexes which will exist for a short
time -- e.g., the duration of a single job. By segregating out all of these
tables, our main application tables and indexes -- which reside in application-
specific tablespaces -- don't have a problem: they don't get dropped, they just
extend periodically when it is necessary to do so.

        Indexes do always get "stale" over time when the tables on which they are based are modified frequently. However, indexes can be dropped and rebuilt
very quickly using the parallel query option, or alternatively using the 7.3
index rebuild command.

        Also, by using standard extent sizes (e.g. one tablespace for 1-mb extents; one for 10-mb extents; one for 100-mb extents), you can avoid having
*unusable* free space altogether -- if a table is dropped and rebuilt with fewer
extents for some reason, the bubbles of free space it leaves will be reused by
any other object that needs to extend.

        If you would like to discuss this further please feel free to email me.

-bn
neumebm_at_hpd.abbott.com Received on Fri May 16 1997 - 00:00:00 CDT