Index tablespace has the most wasted space and yes, there are several
indices in those tablespaces. I need to keep it available or I'd just
drop and recreate the entire index.
good to know I wasn't hallucinating!
- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> Rachel - Right you are, as "Stop Defragmenting . . . " points out,
> there are
> several types of fragmentation.
> - Is it your table or your index that contains wasted space?
> - Are there multiple objects in each tablespace or just a single
> object?
> - Are you trying to keep the index available to users while you're
> rebuilding? I'm assuming this is the reason you are looking at
> rebuilding
> the index twice. Or is it because rebuilding an index probably won't
> cause a
> large sort?
> Overall it looks like a reasonable plan, though I haven't worked much
> with
> partitioned indexes myself.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, August 14, 2003 12:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I never thought I would care about fragmentation again, especially
> once
> I built all my databases using LMTs.
>
> But I've actually found a reason to care, sort of. We have a data
> warehouse with lots of "wasted" space in it. By that I mean, many of
> the partitions (we partition by month) are way larger than is needed
> for the data contained within them. Once all the data for a month is
> loaded, that's it, it doesn't grow anymore.
>
> The oversized tablespaces are those associated with the indexes. Part
> of the problem is that the hosting company we use has a threshhold of
> 80% and when a tablespace is 80% full, they automatically expand the
> datafiles. part of the problem is that for a time there were problems
> with the loads and we had to delete/re-insert/delete/re-insert data.
>
> I want to shrink the datafiles, but they are "fragmented". yes, I
> know
> "disk is cheap", but having gone through a 3 month exercise in
> frustration trying to get the data center management to spend a few
> thousand dollars on more memory so that we could actually run
> reports,
> I'm not going there.
>
> I was planning on doing the following, just wanted a sanity check
> from
> the list:
>
> 1) create a very large holding tablespace, to use as a rebuilding
> area
>
> then, on a partition by partition basis:
>
> a) rebuild the index partition into the holding tablespace (lots of
> indexes to rebuild in each partition)
> b) rebuild the index partition back into the original tablespace (my
> hope is that this will effectively "compress" the index extents)
> c) shrink the index partition datafiles
>
>
> Does this make sense or am I overtired and not thinking? Is there a
> better/faster/EASIER way to do what I wanted to do?
>
> Rachel
>
>
> Rachel
>
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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).
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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 Aug 15 2003 - 13:54:22 CDT
