| 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: fixing tablespace fragmentation
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
> 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
> > >
-- 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).Received on Fri May 16 2003 - 17:03:46 CDT
 |
 |