Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Tablespaces on ALTER TABLE ADD PARTITION

Re: Index Tablespaces on ALTER TABLE ADD PARTITION

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 17 Feb 2006 07:56:53 -0800
Message-ID: <1140191804.793573@jetspin.drizzle.com>


Michel Cadot wrote:
> "Tiff" <tiffanyherpin_at_yahoo.com> a écrit dans le message de news: 1140040396.015401.26450_at_f14g2000cwb.googlegroups.com...
> | Hello!
> |
> | I support 10.1.0 databases on Solaris 8.
> |
> | We have a database where we are using partitions to store and drop data
> | by calendar quarters. Each quater gets a new partition in a given
> | table and consequently, the old partition gets dropped.
> |
> | We can add partitions just fine, but it seems that Oracle is plopping
> | the index into the user's default tablespace. I've combed through the
> | SQL Reference guide and can't find a way to designate which tablespace
> | we want to use for the index on an ALTER TABLE ADD PARTITION command.
> |
> | Any ideas?
> |
> | Thanks!
> |
> | Tiffany
> |
>
> Move the new index partition to the wanted tablespace afterwards
> (alter index ... rebuild partition ... tablespace ...).
> It is immediate as the partition is empty.
>
> Regards
> Michel Cadot

Also look at the capabilities of the built-in package DBMS_PCLXUTIL. The package contsints a single procedure BUILD_PART_INDEX specifically to support the building of partition indexes.

A demo can be found in Morgan's Library at www.psoug.org

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Feb 17 2006 - 09:56:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US