Re: How to reduce fragmentation in one TS?

In article <36B733D1.657CC266_at_us.oracle.com>,   Peter Sharman <psharman_at_us.oracle.com> wrote:
> Pierre-Yves
>
> Yes it will probably cause fragmentation if you are adding mandatory
> columns, maybe not if you're adding nullable ones (depends on the amount
> of data going in). Yes, export/import will remove the fragmentation but
> NO, compress=y is not necessary. This is a common misconception. All
> this will do is put the entire object in one extent (a humongous one
> probably in your case).
>

Just a reminder that compress=y is the default so you may want to code compress=n on the export so that the import does not fail because it can not allocate one giant extent which can happen once the table exceeds the size of the smallest file that makes up the tablespace or if the tablespace does not have a large enough free extent available.

Also to import a large table you will probably need to code commit=y on the import unless you provide a special large rollback segment and make all the regular rollback segments unavailable.

> HTH.
>
> Pete
>
> Pierre-Yves Kerbiquet wrote:
>
> > Hello,
> >
> > I am adding columns to a large table (billion of rows). I am afraid it
> > creates fragmentation.
> > Can I reduce fragmentation by doing export/import of this table?
> > Do I need to specify COMPRESS=Y when exporting data?
> > Do you have a tip to view fragmentation in one TS?
> >
> > Thank you for your help.
> >
> > PYK
>
> --
>
> Regards
>
> Pete
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Peter Sharman Email: psharman_at_us.oracle.com
> WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> Worldwide Internal Services Education (650)607 0109 (local)
> San Francisco
>
> "Controlling application developers is like herding cats."
> Kevin Loney, ORACLE DBA Handbook
> "Oh no it's not! It's much harder than that!"
> Bruce Pihlamae, long term ORACLE DBA
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 03 1999 - 08:52:56 CST