Re: Defrag a tablespace

There's another potential way.

Map out the used and free extents. Find the used extents which are causing fragmentation. Rebuild the objects elsewhere. Perhaps even in the same tablespace. I've done this, on a few 8.0 24x7 databases. It can be like running in front of a steam roller to pick up pennies, but it works.

If you have OEM tablespace manager before version 2, the mapping is done for you and really easy to read. ----- Original Message -----
From: Ruth Gramolini <rgramolini_at_tax.state.vt.us> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, November 09, 2000 9:51 AM Subject: Re: Defrag a tablespace

> The only way to defrag a tablespace, according to ORACLE, is export it and
> import it.
>
> Ruth
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, November 09, 2000 9:31 AM
>
>
> > Dear Oracle Experts,
> >
> >
> > Having run "alter
> > tablespace DEVELOPMENT
> > coalesce' there was no
> > change in the number of
> > chunks after running
> > this command. I suspect
> > that this means that
> > these spread out chunks
> > are not neighboring
> > (contiguous) blocks. Is
> > there a way to export a
> > tablespace, with
> > several different
> > schema owners, to
> > eliminate the
> > fragmentation in this
> > tablespace? It has 501
> > chunks in it while
> > having 396Mb of free
> > space.
> >
> > Would I need to do a
> > complete export of the
> > database, drop and
> > recreate the fragmented
> > tablespace, then import
> > with ignore=Y or is
> > there another way?
> > (This is on a 8.0.4 Db
> > on a Sun, if that
> > helps.) Also I could
> > write a query to grep
> > all the tables in this
> > tablespace, taking care
> > to preserve referential
> > integrity, export all
> > the tables, grep by
>
> > query, then recreate
> > the tablespace and
> > import.
> >
> > I could not find an
> > export tablespace
> > script. Does anyone
> > have one they are
> > willing to share?
> >
> >
> >
> >
> > Thanks in advance for your time.
> >
> > Regards,
> > Phil Lima
> > SCT Global Government Solutions
> > plima_at_sctcorp.com
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Philip Lima
> > INET: plima_at_sctcorp.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ruth Gramolini
> INET: rgramolini_at_tax.state.vt.us
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Nov 09 2000 - 14:58:44 CST