要将一个分区索引挪到指定的表空间，首先检查一下：

Segment Name Size In MB Tablespace Name Segment Type--------------------------- ---------- -------------------- ------------------MISC16.PK_SERVSTAT 6 INDEX_HIS02 INDEX PARTITIONMISC16.PK_SERVSTAT 6 INDEX_HIS03 INDEX PARTITION

直接使用alter index ... rebuild tablespace ...肯定是不行的，你会收到ORA-14086报错，例如：SQL> alter index MISC16TEST.PK_SERVSTAT rebuild tablespace DATA_DYNAMIC nologging;alter index MISC16TEST.PK_SERVSTAT rebuild tablespace DATA_DYNAMIC nologging *ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a whole

SQL>

我们需要使用：ALTER INDEX <索引名称> REBUILD PARTITION <索引分区名称> [tablespace 表空间] [nologging];语法来重建分区索引：SQL> ALTER INDEX MISC16TEST.PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC nologging;ALTER INDEX MISC16TEST.PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC nologging *ERROR at line 1:ORA-14185: incorrect physical attribute specified for this index partition

SQL>

关于ORA-14185 的解释：Error: ORA-14185Text: incorrect physical attribute specified for this index partition ---------------------------------------------------------------------------Cause: unexpected option was encountered while parsing physical attributes of a local index partition; valid options for Range or Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, PCTUSED, LOGGING and TABLESPACE; but only TABLESPACE may be specified for Hash partitions STORE IN () is also disallowed for all but Composite Range partitions Action: remove invalid option(s) from the list of physical attributes of an index partition

总结：这个 ORA-14185 其实是oracle的 Bug 1890073：

一般来说，带有下面这些的选项的rebuild index语句会遇到ORA-14185错误：logging nologging initrans 10 maxtrans 10 compress nocompress storage (next 1m) storage (buffer_pool default)

而带有下面这些的选项的rebuild index语句则不会遇到ORA-14185错误：parallel noparallel pctfree 10 compute statistics online tablespace system

例如：SQL> ALTER INDEX PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC;

Index altered.SQL>