Oracle-L: fragmentation

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Thu, 14 Aug 2003 09:29:23 -0800
Message-ID: <F001.005CAB16.20030814092923@fatcity.com>

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:

create a very large holding tablespace, to use as a rebuilding area

then, on a partition by partition basis:

rebuild the index partition into the holding tablespace (lots of indexes to rebuild in each partition)
rebuild the index partition back into the original tablespace (my hope is that this will effectively "compress" the index extents)
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

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 Thu Aug 14 2003 - 12:29:23 CDT