RE: Tablespace reorg tools

Jarad

Even we have pct_increase = 0 ??
Is it oracle 8 feature??

I believe 7.3.4 doesn't support this.

Sameer

-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com] Sent: Wednesday, April 26, 2000 11:53 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tablespace reorg tools

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