Re: fragmentation of a tablespace

Why drop and re-create the tablespace? That's too drastic a step to defragment it. If you are using Oracle 7.3, you can issue:

  ALTER TABLESPACE harvest202 COALESCE;

This makes the free space contiguous again which will de-fragment it. There is also an event you can set for versions below Oracle 7.3 (see Rama Velpuri's "Oracle backup and recovery" book). I would drop and create a tablespace only if:

1. You want to create it as a single file rather than multiple files (say you had to ADD DATAFILE at some point and you want just one, large file).
2. You want the single file to be enlarged manually (with Oracle 7.2, you can use the AUTOEXTEND feature to enlarge a file so there is no need for this - unless, the file is a raw device).

Also, remember that dropping a table drops the grants on that table and invalidates objects that depend on it (such as stored procedures). Rather than dropping the table, disable foreign key constraints that depend on it then just TRUNCATE the table. After issuing COALESCE and re-importing the table date, re-enable the foreign key constraints.

  HTH. Rafael S. Ramirez raffy_at_erika.upd.edu.ph Independent Oracle Consultant Professor, Department of Electrical and Electronics Engg. Snail Mail: c/o EEE Dept., University of the Philippines, Diliman QC 1101

On Wed, 20 Nov 1996, Dick Allie wrote:

> 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 Sat Nov 23 1996 - 00:00:00 CET