Re: Tablespace fragmentation

 I typically use alter table move. I find it is easier to use. Remember to rebuild all of the table's indexes after you  do the move because they will go invalid.

Also you may wish to consider using Locally managed tablespaces with uniform sizes so you can avoid fragmented tablespaces in the future.

When you move a table without specifying space parameters in a dictionary managed tablespace the
table will be rebuilt using it's original sizing in the same tablespace. This may or may not ease your tablespace fragmentation problems. (It probably will not)

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com

Author of: 'So You Want to be an Oracle DBA?'

"ksmith" <NoSpam_at_erieplating.com> wrote in message news:umvl8sbn5mno22_at_corp.supernews.com...
> Oracle 8i on Linux RH 7.2
>
> I planning on addressing the fragmentation issue we have in a couple of
our
> Oracle 8i tablespaces. In my reading of the subject (being that I am kind
of
> new at this), I was confused about the benifits/downfalls of exporting and
> importing as opposed to using the MOVE command.
>
> I would rather not export and drop all of my objects if the MOVE command
is
> just as efficient. Our fragmentation is not severe enough to warrent
> rebuilding all of our tables and taking the time for a export, however if
it
> is best for performance I would do it.
>
> Also, concerning the MOVE command; If I use the following command:
>
> Alter table xxx MOVE TABLESPACE original
> *****moving the table to the same tablespace it is already in******
>
> Will this rebuild the table AND eliminate fragmentation? I have read both.
I
> would just try it, but I don't have a test db at the moment (the world is
an
> imperfect place). If you could give me some input, I would really
appreciate
> it.
>
>
Received on Sun Sep 01 2002 - 13:09:07 CDT