Re: fixing tablespace fragmentation

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