RE: Tablespace reorg tools

Hmmmmm.......................

you had time to read and answer? I must have been busy today :)

Rachel

>From: Steve Boyd <pimaco_oracle_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Tablespace reorg tools
>Date: Wed, 26 Apr 2000 08:37:09 -0800
>
>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).
>
>__________________________________________________
>Do You Yahoo!?
>Send online invitations with Yahoo! Invites.
>http://invites.yahoo.com
>--
>Author: Steve Boyd
> INET: pimaco_oracle_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
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
Received on Wed Apr 26 2000 - 19:00:26 CDT