It depends on the situation.
In a previous project (Oracle 8), I've put each
partition in its own tablespace. I was lucky that for
each fact tables the partition were the same size.
If you're partitionning by time, it eases data
suppression (drop partition and drop tablespace).
If you have too many small partitions, having them in
a specific tablespace will create more management than
gain.
"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."
The developpers are deciding what to do with the
data ??? Shouldn't be the end-users who decided what
to do with their data ?
- Binley Lim <Binley.Lim_at_ird.govt.nz> wrote:
>
> Hmmm, it depends on the situation. There are pros
> and cons in both, but certainly partitions should be
> in partitions-only tablespaces.
>
> Unless you can size the partitions with great
> accuracy, you would have to build in reasonably
> free-space in the TBS. If you have overdone this,
> you would have to come back and resize. This would
> be a very tedious way of managing 100 tablespaces.
>
> If you group the partitions in some logical manner,
> you can put them in the same TBS, and pool the
> free-space to allow for un-predicted growth, and
> minimise waste.
>
> I am unclear why exp/imp is going to free up more
> space than dropping partitions, unless its severely
> fragmented. Keeping to uniform extents will get rid
> of this problem.
>
> >>> kirti.deshpande_at_verizon.com 04/12/01 08:12AM >>>
> Hi Cherie,
> I am in favor of one tablespace per partition. For
> availablity and
> maintenance purposes it certainly helps us in our
> environment. I have
> implemented this concept in our data mart database
> which is is now around
> 90GB. There are multiple datafiles per tablepace
> each representing an extent
> (~500 MB). We load data to this database once a
> month, so I am not too
> concerned with the checkpoint times etc. (due to a
> number of data files).
> There are 4 large tables with 10 partitions so far
> (one per year). Each
> table has 2-3 indexes, all locally partitioned.
> Again each in its own
> tablespace. Partitioning key is the 4 digit year.
> All tablespaces and
> partition names contain the 2 digit year for ease of
> understanding what's in
> them. Partitioned table data and partitioned index
> data is placed on
> physically separate storage devices. Since, all the
> data comes from other
> legacy (or otherwise, internal) systems, we do not
> have a partition for
> 'MAXVALUE', and that saves me a lot of work when
> adding new partitions for
> new years (did that for 2000 and 2001). Working very
> well for the last 2
> years now, started out with 8.0 and recently
> upgraded to 8.1.7. All this was
> done as a parallel process and soon (next month) it
> will all be 'real'
> production with one more set of storage devices to
> split data and index
> further.
>
> HTH..
>
> - Kirti Deshpande
> Verizon Information Services
> http://www.superpages.com
>
> > -----Original Message-----
> > From: Cherie_Machler_at_gelco.com
> [SMTP:Cherie_Machler_at_gelco.com]
> > Sent: Wednesday, April 11, 2001 2:06 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: 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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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: Binley Lim
> INET: Binley.Lim_at_ird.govt.nz
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
=== message truncated ===
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.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 Thu Apr 12 2001 - 04:23:12 CDT
