Tablespace Fragmentation

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