Re: Tablespace Fragmentation

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+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

gangadnx_at_jmu.edu (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.
Received on Fri May 16 1997 - 00:00:00 CDT