Re: Defrag a tablespace

The only way to defrag a tablespace, according to ORACLE, is export it and import it.

Ruth

• Original Message ----- From: "Philip Lima" <plima_at_sctcorp.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 09, 2000 9:31 AM Subject: Defrag a tablespace

> 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
Received on Thu Nov 09 2000 - 09:50:00 CST