How do you move a partitionned index from one TS to another ?

[Nicolas Dumeige]

Hello,

How do you move a partitionned index from one TS to another ?

Thanks

[Jean-Luc Oudart]

What about
alter index rebuild tablespace

Regards,
Jean-Luc

[Thierry Poels_1]

Hi,


Move the index partition to a new tablespace with the following command:

alter index index_name rebuild partition part_name tablespace tsname;

(hope you're talking about Oracle ;-)
good luck,
Thierry.

[Nicolas Dumeige]

The second answer is better.

As you can see Jean-Luc, Oracle refused to move the whole index at once.

14:55:43 RSTX01> alter index ISLDRST_CPTEXT_RST_SAVE rebuild tablespace RST;
alter index ISLDRST_CPTEXT_RST_SAVE rebuild tablespace RST
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

Do I really need to specify every partition_name ?

Thanks

[Thierry Poels_1 · Accepted solution]

hi,

yep, normally every partition resides in a different tablespace.
But you can combine them into one "alter index" statement:

alter index I1
rebuild partition P1 tablespace T1 storage (...)
rebuild partition P2 tablespace T2 storage (...);

good luck,
Thierry.

[Jean-Luc Oudart]

I just wanted to give general idea.
not using partioning.

from the documentation :
Using Alter Index to Rebuild a Partition
The ALTER INDEX...REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used on a composite-partitioned table. At the same time as you recreate the index, you can move the partition to a new tablespace or change attributes.

For composite-partitioned tables, use ALTER INDEX...REBUILD SUBPARTITION to rebuild a subpartition of an index. You can move the subpartition to another tablespace or specify a parallel clause. The following statement rebuilds a subpartition of a local index on a table and moves the index subpartition is another tablespace.

ALTER INDEX scuba
REBUILD SUBPARTITION bcd_types
TABLESPACE tbs23 PARALLEL (DEGREE 2);


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Also there was a bug on 8i where the statistics were lost. Bug 3018073
Don't know your version.


Regards,
Jean-Luc

[Nicolas Dumeige]

Merci à vous deux pour votre aide.

J'ai utilisé le script suivant pour reconstruire / déplacer mon index :

select 'ALTER INDEX '||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||' TABLESPACE RST'
from USER_IND_PARTITIONS
where INDEX_NAME in (SELECT index_name FROM user_indexes WHERE table_name='TSLDRST_SAV