Re: fragmentation of a tablespace

ythorne_at_acs.neu.edu wrote:
>
> I have a small database (85mg) with six tablespaces (system, rbs, temp,
> tools, users, harvest202). The harvest202 tablespace (20mg) is
> fragmented. The other tablespaces are fine.
>
> The Oracle7 Server Utility User's Guide (Export/Import Tips) recommends
> the following procedure for reducing database fragmentation:
> 1. Do a full database export (full=y) to backu up the entire db.
> 2. Re-create the database using the "create database" command.
> 3. Do a full database import (full=y) to restore the entire db.
>
> Is it possible to defrag a tablespace without exporting/importing the
> entire database?
>
> Thanks for your input!

To defrag a tablespace you can set up an export for all the tables in that tablespace using the tables= option on the export and the compress=Y option. Then drop all the tables in the tablespace. Next drop the tablespace and then recreate it. Next import the dump file from the above export yusing the full=y option. If you want you can set up scripts to rebuild the tables in the tablespace prior to the import and use the ignore=Y option on the import.

Problems that can arise are default column values and column constraints do not get set up properly. If the tablespace in question has objects from one user and that's user's objects only reside in that tablespace you can export using the user=..... instead of the tables=Y option.

This is kind of sketchy but you should see the concept. If you need more detail info contact me by email.

Dick Allie - email = dallie_at_ionet.net Received on Wed Nov 20 1996 - 00:00:00 CET