| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Defrag a tablespace
On the older versions of Oracle (7.3.4) you had to issue the "alter..coales=
ce" command 12 times before the SMON would coalesce the tablespace. What =
is the pctincrease value of the tablespace? If it is set to non-zero, SMON =
will automatically coalesce the tablespace after awhile.
There is no command that I have heard of for exporting a tablespace. You =
have to export the individual tables, drop the tables, recreate the =
tables, and then import the data back into the tables.
Be cautious of the order in which you create the tables in the tablespace. =
You could end up in the same situation you are in today(not a large enough =
space left to create a large table). I have always created the large =
tables first and then the smaller ones would fit okay.
ROR m=AA=BF=AAm
>>> plima_at_sctcorp.com 11/09/00 09:31AM >>> Dear Oracle Experts,
=
=20
Having run "alter =
=20
tablespace DEVELOPMENT =
=20
coalesce' there was no =
=20
change in the number of =
=20
chunks after running =
=20
this command. I suspect =
=20
that this means that =
=20
these spread out chunks =
=20
are not neighboring =
=20
(contiguous) blocks. Is =
=20
there a way to export a =
=20
tablespace, with =
=20
several different =
=20
schema owners, to =
=20
eliminate the =
=20
fragmentation in this =
=20
tablespace? It has 501 =
=20
chunks in it while =
=20
having 396Mb of free =
=20
space. =
=20
=
=20
Would I need to do a =
=20
complete export of the =
=20
database, drop and =
=20
recreate the fragmented =
=20
tablespace, then import =
=20
with ignore=3DY or is =
=20
there another way? =
=20
(This is on a 8.0.4 Db =
=20
on a Sun, if that =
=20
helps.) Also I could =
=20
write a query to grep =
=20
all the tables in this =
=20
tablespace, taking care =
=20
to preserve referential =
=20
integrity, export all =
=20
the tables, grep by =
=20
query, then recreate =
=20
the tablespace and =
=20
import. =
=20
=
=20
I could not find an =
=20
export tablespace =
=20
script. Does anyone =
=20
have one they are =
=20
willing to share? =
=20
=
=20
Thanks in advance for your time.
Regards,
Phil Lima
SCT Global Government Solutions
plima_at_sctcorp.com=20
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
--=20
Author: Philip Lima
INET: plima_at_sctcorp.com=20
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 - 11:06:51 CST
 |
 |