thanks to everyone for their input.
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Friday, May 16, 2003 6:03 PM
> I think Ed is referring to row migration (often mistakenly referred to as
> row chaining, even by Oracle in USER_TABLES). This could be called
> fragmentation, and the paper "How to Stop Defragmenting and Start Living"
> does refer to it as heap fragmentation. Since Oracle has several types of
> tables now -- partitioned, index-organized, plain old tables are now
called
> heap tables. And while as Jared says, LMT will prevent tablespace
> fragmentation, it won't help heap fragmentation.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Friday, May 16, 2003 4:23 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Ed,
>
> A tablespace is fragmented when it has free space that cannot easily
> be used. It's not unusual to see many small chunks of free space in
> a DD managed extent that cannot be easily used. ie. there are 200
> chunks of 5 blocks each scattered all over your tablespace.
>
> In an LMT, this can't happen. All chunks of freespace are the same
> size. ( with uniform extent sizing ). It doesn't matter how many of them
> there are, thay can *all* be used by new objects, or objects that are
> extending.
>
> Jared
>
>
>
>
>
>
> "Ed Lewis" <eglewis_at_hotmail.com>
> Sent by: root_at_fatcity.com
> 05/16/2003 01:43 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: fixing tablespace fragmentation
>
>
> Hi Richard,
> The LMT does have a uniform size.
> Maybe it's my interpretation of fragmentation
> on the tablespace level :
> when I query "dba_free_space" for a particular ts, and it
> returns a count > 1, I consider the ts fragmented.
> thanks.
> ed
>
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, May 16, 2003 11:47 AM
>
>
> > Hi Ed,
> >
> > If your LMT had a uniform size, I would be most interested in seeing how
> the
> > *tablespace* got fragmented to the point where you had unusable space
> > (except at the end of a file if you didn't size it right and considered
> the
> > bitmaps blocks). Unusable space being my definition of fragmentation
> > (problems).
> >
> > The parameters you listed (which are set most dangerously to say the
> least)
> > do not effect *tablespace* fragmentation.
> >
> > Cheers
> >
> > Richard
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Saturday, May 17, 2003 12:31 AM
> >
> >
> > > Not always true. I've setup a database
> > > for a COTS application, using LMT's
> > > and have experienced fragmentation.
> > > I'm not positive what caused the problem
> > > but I think it may be a few things.
> > >
> > > All the tables were set up with
> > > pctused = 95, and pctfree =5 by the vendor.
> > > A few of these tables had chained rows.
> > > These same tables had around 12 numeric
> > > columns. So, I figured as the tables
> > > (which were initially empty) became populated
> > > with data, and with the small "pctfree"
> > > chaining occurred.
> > > I adjusted these parameters on the
> > > trouble tables, and rebuilt them
> > > and that seemed to help.
> > >
> > > ed
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Thursday, May 15, 2003 8:26 PM
> > >
> > >
> > > > Consider reorging them into LMTs and then you can kiss fragmentation
> > > > goodbye.
> > > >
> > > > Dennis Williams
> > > > DBA, 80%OCP, 100% DBA
> > > > Lifetouch, Inc.
> > > > dwilliams_at_lifetouch.com
> > > >
> > > >
> > > > -----Original Message-----
> > > > Sent: Thursday, May 15, 2003 8:52 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > We are using dictionary managed tablespacse and yes I know I should
> use
> > > > locally managed, but I am not allowed to change.
> > > >
> > > > I have significant fragmentation. I moved all my tables to a new
> > > tablespace,
> > > > rebuild them properly so that they would not fragment(kept the
> Initial
> > and
> > > > Next extents the same) compacted them to PCTUSED 99 and PCTFREE
> 1(wont
> > be
> > > > any inserts,updates or deletes)
> > > >
> > > > I coalesced the tablespace, but was unable to resize it. My indexes
> are
> > > > fragmented too. Is there a simple command similiar to
> > > >
> > > > ALTER TABLE MOVE
> > > >
> > > > in order to move my indexes to the new tablespace? What is the best
> > method
> > > > to move indexes to a new tablespace? Should I generate a script to
> > rebuild
> > > > them and then drop them?
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: <rgaffuri_at_cox.net
> > > > INET: rgaffuri_at_cox.net
> > > >
> > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
> services
> > > >
> ---------------------------------------------------------------------
> > > > 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).
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: DENNIS WILLIAMS
> > > > INET: DWILLIAMS_at_LIFETOUCH.COM
> > > >
> > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
> services
> > > >
> ---------------------------------------------------------------------
> > > > 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).
> > > >
> > > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Ed Lewis
> > > INET: eglewis_at_hotmail.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > 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).
> > >
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Richard Foote
> > INET: richard.foote_at_bigpond.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ed Lewis
> INET: eglewis_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Edward Lewis
INET: eglewis_at_hotmail.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Sat May 17 2003 - 12:56:46 CDT
