Newsgroups: comp.databases.oracle.server
Subject: Re: Tablespace Fragmentation
References: <5li9d0$1b8@owl.jmu.edu>
gangadnx@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.
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: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page