Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Separate Indexes and Data

Re: RE: Separate Indexes and Data

From: Rachel Carmichael <>
Date: Tue, 30 Sep 2003 10:34:28 -0800
Message-ID: <>

Nuh uh, not me... I have never used or experimented with auto-allocate.

I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space.

I've just completed writing the scripts for the following:

we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month).

since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to.

If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space  

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
Please see the official ORACLE-L FAQ:
Author: Rachel Carmichael

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Tue Sep 30 2003 - 13:34:28 CDT

Original text of this message