RE: fixing tablespace fragmentation

Ed - You've cheered me up. The vendors are constantly striving to make their product idiotproof. Great news that they have not yet achieved their goal. You are correct that fragmentation can occur at several levels, and this is a type of fragmentation that LMT won't cure. But that is just the sort of complexity that may keep me employed for a few more years.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, May 16, 2003 9:32 AM
To: Multiple recipients of list ORACLE-L

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