Re: fragmentation of a tablespace

Naren Chintala 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!
>
> 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

Naren,

I don't think this would do a defrag on the tablespace. All you are acheiving doing this is coalescing adjacent free extents into one large extent.

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.