Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitions each in a separate tablespace ?

Re: Partitions each in a separate tablespace ?

From: Jay Hostetter <>
Date: Wed, 13 Dec 2000 11:10:44 -0500
Message-Id: <>

I generally put each partition in a separate tablespace. One of our = tables requires a new partition each month, while dropping off the oldest = partition. One of the reasons that I use separate tablespaces is to avoid = any tablespace fragmentation issues.

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

>>> "djordjej" <> 12/12/00 09:50PM >>>

I am creating partitions on a table, and need to decide whether to put partitions in separate tablespace (a tablespace for each partition) or to put them all together.

Here is my reasoning:

Main advantages of partitions is better performance, and easier maintenance= .

As far as performance goes in my case only the last (most recent) = partition
will be used by the application, so there is no big advantage of spreading the load between different tablespaces (btw my database is on a filer so I am not spreading load between physical disks anyway). As far as maintenanc= e
goes, for dropping of the old (historic) partitions I don't see any difference whether they are in separate tablespaces or in the same one. = As
far as a possible recovery (file corruption, etc.) goes it seems to me = that
for data partitions there is just a minor advantage of having each = partition
in separate tablespace, so that only that one corrupted partition = datafile
has to be copied for the backup, instead of a bigger datafile (for all partitions). As far as index tablespaces go there might be a significant difference if one of the local index partitions is corrupted, only that index need to be rebuilt, comparing to rebuilding all the local indexes in case they are all in the same partition.

So I am inclined to put index partitions each in a separate tablespace, = and
I am not sure about data partitions.

Any other thoughts, experience, suggestions ?



Please see the official ORACLE-L FAQ: --=20
Author: djordjej

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: (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 Dec 13 2000 - 10:10:44 CST

Original text of this message