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

One tablespace per partition.

If you're not sure of the final size of the partition, consider putting each tablespace file
with autoexend ON and specify max filesize (MAXSIZE).

For example: if partition extent size is 10M, the autoextend value could be 100M to have room for 10 more extents. This tip helps avoid a lot of wasted space AND/OR getting calls in the middle of the night to make the filesize larger. Be sure the autoextend does not attempt to extend the filesize beyond the capacity of the disk. Sizes chosen would depend upon your knowledge of the partition size needed to hold the data and your strategy. Could also autoextend at 10M, but performance would be affected if a lot of new extents were added during a load.

Syntax:
CREATE TABLESPACE CUST_DATA_0401
   DATAFILE '/u5/oracle/oradata/data/cust_data_0401.dbf'    SIZE 100M

        AUTOEXTEND ON
        NEXT 100M
        MAXSIZE 500M;

and the corresponding index - file size dependent upon how many indexes present on the table and how large the column sizes are. If you have five indexes (in a datawarehousing environment), the size of the index tablespace COULD be larger than the corresponding data tablespace.

CREATE TABLESPACE CUST_IDX_0401
   DATAFILE '/u06/oracle/oradata/index/cust_idx_0401.dbf'    SIZE 50M

        AUTOEXTEND ON
        NEXT 10M
        MAXSIZE 300M;

Jenny Jacobson
Oracle DBA Consultant
Member - RMP Consulting Partners LLC - Member NAACB Available for project

On Wed, 11 Apr 2001 Cherie_Machler_at_gelco.com wrote:

>
> 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: Jenny Jacobson
  INET: jenny_at_dsl.pitt.edu

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