| 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: Tablespace reorg tools
Deepak,
I believe COMPRESS=Y will resize the initial extent, but the next extent will remain the same size. If initial was 16K and next was 16K, and you have inserted a few thousand records, and export with COMPRESS=Y, oracle will recalculate the initial (so all records fit in the initial extent) *but* the next extent will still be 16K. This is usually a *bad* thing.
I know that's the case with v8.0.5 on DG.
Does anyone know if the same applies to v8.1.x?
> -----Original Message-----
> From: Deepak Sharma [SMTP:sharmakdeep_at_yahoo.com]
> Sent: Wednesday, April 26, 2000 3:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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:
> >
> === message truncated ===
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
> --
> Author: Deepak Sharma
> INET: sharmakdeep_at_yahoo.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
Received on Wed Apr 26 2000 - 14:53:58 CDT
 |
 |