Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index Tablespaces on ALTER TABLE ADD PARTITION
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