Re: Should each partition have it's own tablespace and datafile?

In my experience, a separate tablespace per partition is advisable; you don't get the full benefit of partition management options otherwise.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 11, 2001 3:05 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: paulb_at_instipro.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).