RE: Tablespace reorg tools

Plus if you set up all segments in a given tablespace to use the exact same extent sizes, then coalescing is a non-issue.

• Jared Still <jkstill_at_bcbso.com> wrote:
  >
  > 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:
  > > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
  > > >
  > > >
  > > >
  > > >
  > > >
  > > >
  > > >
  > > > ---
  > > > You are currently subscribed to oracle as: rdmorton_at_switch.com
  > > > To unsubscribe send a blank email to leave-oracle-76034V_at_telelists.com
  > > > or visit the Oracle mailing list on the Web at:
  > > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
  > >
  > > ---
  > > You are currently subscribed to oracle as: gortiz_at_houston.omnes.slb.com
  > > To unsubscribe send a blank email to leave-oracle-102619X_at_telelists.com
  > > or visit the Oracle mailing list on the Web at:
  > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
  > >
  > >
  > > ---
  > > You are currently subscribed to oracle as: jkstill_at_bcbso.com
  > > To unsubscribe send a blank email to leave-oracle-20875Y_at_telelists.com
  > > or visit the Oracle mailing list on the Web at:
  > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
  > >
  >
  >
  > Jared Still
  > Certified Oracle DBA and Part Time Perl Evangelist ;-)
  > Regence BlueCross BlueShield of Oregon
  > jkstill_at_bcbso.com - Work - preferred address
  > jkstill_at_teleport.com - private
  >
  >
  > --
  > Author: Jared Still
  > INET: jkstill_at_bcbso.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
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).