| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Should each partition have it's own tablespace and datafile?
We have a 120 Gig datawarehouse that uses more than 100 date-based partitions. For financial and political reasons, we have run out of disk space to give this database as it continues in it's relentless growth.
The developers have resigned themselves to purge out some of the old data and get rid of partitions that are more than two years old.
We currently have about ten tablespaces that contain all of the data and indexes for this database. There is a small, medium, and large tablespace for data and for indexes plus some other specialized tablespaces by functionality.
Anyway, dropping a couple dozen of these partitions is not going to be enough to free up some disk space for us. Instead we're going to have to export our data, drop the tablespaces, and recreate them as a smaller size and then reimport the data minus the dropped partitions.
Since we're going to all of this work, I'm wondering if we should reorg these partitions by creating a tablespace for each partition and a single datafile for each tablespace. That way in the future, every time we want to drop a partition, it will be very easy to reclaim the disk space associated with that partition. I don't want to have to do these reorgs every month.
How do most places physically lay out their partitions? What is the downside of having a datafile for each partition? Wasted space? Would a compromise be to assign six months worth of partitions to a single tablespace?
We are currently on version 8.0.4 of Oracle on Sun Solaris 2.6 but we will be upgrading soon to 8.1.7. Looks like I may need to reorg this before we can upgrade as we are rapidly running out of room.
Thanks,
Cherie Machler
Gelco Information Network
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Cherie_Machler_at_gelco.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------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 Wed Apr 11 2001 - 13:35:21 CDT
 |
 |