Subject: Re: Tablespace Fragmentation
References: <5li9d0$> (Nadira Gangadhar) writes:
>We're running Oracle 7.1.6 and 7.3.2 on HPUX and need some help with tablespace 
>fragmentation problems.  We routinely run a script (on 7.1.6 databases) that 
>was created in-house to defrag tables and indexes (through export and import), 
>and we use a script that forces a coalesce of contiguous free extents in 
>tablespaces, which is from Oracle Support.  The tablespace coalesce does not 
>get all the disjointed bits of free space (looked at user_free_space).  There 
>is therefore a lot of total free space but in small chunks, so we have to keep 
>adding datafiles.  For 7.3.2 databases the tablespace coalesce and rebuild 
>index commands are used.  

>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.

>Thanks for any help.
Exporting, dropping, and importing everything will do the job. You are
already exporting, dropping, and importing via your in-house script, so I
don't think the process will be any slower. It just depends on if you were
routinely doing this for ALL tables/indexes.

Other parameters to look at:

* Are your PCT_INCREASEs 0 or 100? Making it anything else causes the
fragmentation you describe.
* Are your INITIAL EXTENT/NEXT EXTENT exact multiples of your
db_block_size? If not you will also encounter fragmentation.

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 60+ Oracle technical tips, visit my Web Page:             <->
<->                                                               <->
<->                                    <->
<->                                                               <->
<->             email:                    <->

Back to Ari Kaplan's Home Page tm">Back to Ari Kaplan's Home Page