RE: Tablespace reorg tools

My understanding is that this is precisely what COMPRESS=Y will accompalish for you while exporting. You needn't create an empty table. The export DDL will have the initial and next sizes appropriately set, based upon the extents info at the time of export.

--Deepak

• "Jain, Akshay" <Akshay.Jain_at_cit.com> wrote:
  > Another question. According to my understanding,
  > When exporting and importing for defragmentation, if
  > the
  > table is not explicitly created with a custom set of
  >
  > storage parameters, it will take its parameters from
  > the
  > DDL in the export dump file. This is undesirable.
  > Thus
  > one *must* have means of creating the empty table
  > before
  > reimporting, to get true control over fragmentation
  > (?).
  >
  > Automating this would have to be a matter of
  > appropriate
  > scripts/tools. One would need to be able to define
  > all
  > dependent objects, disable triggers before the data
  > import,
  > properly allocate the index to appropriate
  > tablespaces with
  > similiar concern to storage settings, etc.
  >
  > This subverts default settings that may come from
  > tablespace
  > definitions.
  >
  > Please correct me where I am wrong.
  > :-)
  >
  > Akshay Jain
  > _______________________
  > Newcourt-CIT
  > Tel. (416) 507-5385
  > mailto:Akshay.Jain_at_cit.com
  > _______________________
  >
  >
  > -----Original Message-----
  > Sent: Wednesday, April 26, 2000 11:53 AM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  >
  > Why would you see an increase in fragmentation?
  >
  > You may see more fragments in the free space, but
  > they will be coalescable.
  >
  > Part of Oracle's extent allocation algorithm is to
  > coalesce free space as needed.
  >
  > Whether you coalesce it up front, or wait until you
  > actually need it, the effects on fragmentation
  > will be the same.
  >
  > Jared
  >
  > On Tue, 25 Apr 2000, Gregorio Ortiz wrote:
  >
  > > boys||girls
  > >
  > > Effect of pct_increase 0
  > >
  > > If one uses PCT_INCREASE 0, you will see an
  > increase in fragmentation. I
  > > usually use 1 in order for the tablespaces
  > > to coalesce automatically via SMON. Unless you are
  > proactive and manually
  > > coalescing the tablespaces at regular intervals,
  > you will be fine. By all
  > > means, do not leave the default of 50%.
  > >
  > > /gxodba
  > >
  > > -----Original Message-----
  > > From: bounce-oracle-102619_at_telelists.com
  > > [mailto:bounce-oracle-102619_at_telelists.com]On
  > Behalf Of Morton, Ronald D
  > > Sent: Tuesday, April 25, 2000 3:21 PM
  > > To: oracle list
  > > Subject: RE: Tablespace reorg tools
  > >
  > >
  > > Correct me if I'm wrong but I believe that the
  > default value for
  > PCTINCREASE
  > > is 50.
  > > You must specifically set it to zero if you want
  > to have full control over
  > > your extents.
  > > I set all of mine to zero in the DEFAULT STORAGE
  > clause when I create a
  > > tablespace.
  > >
  > > HTH,
  > > Ron
  > >
  > > > -----Original Message-----
  > > > From: Lisa_Koivu_at_gelco.com
  > [SMTP:Lisa_Koivu_at_gelco.com]
  > > > Sent: Tuesday, April 25, 2000 11:21 AM
  > > > To: oracle list
  > > > Cc: oracle list
  > > > Subject: Re: Tablespace reorg tools
  > > >
  > > > Ana, have you tried 'TRUNCATE TABLE DROP
  > STORAGE' and removing the
  > > > PCTINCREASE?
  > > > I never use pctincrease, I want to specifically
  > state the size of the
  > > > extents.
  > > > Others may disagree...
  > > >
  > > > I don't see why you would have to reorg if you
  > are truncating.
  > Truncating
  > > > is
  > > > the first step in reorging tables. I wish I
  > could truncate some of my
  > > > tables
  > > > daily.
  > > >
  > > >
  > > >
  > > >
  > > >
  > > > achoto_at_american.edu on 04/25/2000 10:09:03 AM
  > > >
  > > > Please respond to achoto_at_american.edu
  > > >
  > > > To: "oracle list" <oracle_at_telelists.com>
  > > > cc: (bcc: Lisa Koivu/GELCO)
  > > >
  > > > Subject: Tablespace reorg tools
  > > >
  > > >
  > > >
  > > > We're having problems with two tablespaces that
  > have plenty of free
  > space,
  > > > but, Oracle doesn't seem to recognize it. We
  > truncate some tables and
  > > > reload them every night, but once in a while one
  > or two large tables
  > fail
  > > > to load because the next available extent is not
  > large enough. All of
  > our
  > > > tables have an initial extent of 81920, 50%
  > increase, minimum extents 1,
  > > > and maximum extents 1017. I suspect that the
  > 50% increase may be the
  > > > culprit. Do we need to look closely at this and
  > maybe change it?
  > > >
  > > > We're on Oracle 8.0.5 in a Sun box. By looking
  > at the tablespace map I
  > > > can
  > > > see that there appear to be a lot of
  > fragmentation. There are many free
  > > > blocks scattered in the middle, but everything
  > to the top and bottom of
  > > > the
  > > > tablespace appear full.
  > > >
  > > > My question is, other than exporting, dropping
  > the tables, and importing
  > > > them, is there another way to reorganize the
  > tables in the tablespace?
  > > > We'd like to do this with as little disturbance
  > to our users as
  > possible.
  > > >
  > > > Are there any tools that someone out there in
  > Oracleland can recommend
  > to
  > > > keep track of fragmentation and to reorganize
  > tablespaces?
  > > >
  > > > Any light that someone in this list can shed
  > will be greatly
  > appreciated.
  > > >
  > > > Thanks
  > > >
  > > > Ana E. Choto
  > > > Systems Programmer
  > > > American University
  > > > Office of Information Technology
  > > > Phone (202) 885-2275
  > > > Fax (202) 885-2224
  > > >
  > > >
  > > > ---
  > > > You are currently subscribed to oracle as:
  > lisa_koivu_at_gelco.com
  > > > To unsubscribe send a blank email to
  > leave-oracle-119953R_at_telelists.com
  > > > or visit the Oracle mailing list on the Web at: