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).