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!

If you are using Oracle 7.3.x use the following

SQL> alter tablespace <tablespace_name> coalesce;

FOr earlier versions try this

SQL> alter session set events 'immediate trace name coalesce level 4';

where 4 is the ts# from the sys.ts$ table. (select ts#,name from sys.ts$;)

Cheers

Naren Chintala
naren_at_mink.att.com Received on Wed Nov 20 1996 - 00:00:00 CET