Move tablespace for a partitioned index
Hi,
We have a table with subpartition (hash) and partition (range). There are some composite indexes defined on the table. We are working on a project to move objects to some newly created tablespaces. As of now we have successfully moved tablespaces of subpartition, partition and tables.
Index subpartitions also have been moved but while moving index partitions we are facing issues. Please suggest how to move tablespace for index partition as well as the index. We need to move index partitions from APPLSYSX to APPS_TS_TX_IDX.
DB Version: 10.2.0.4
SQL> alter index APPLSYS.WF_ITEMS_PK REBUILD PARTITION WF_ITEM1 TABLESPACE APPS_TS_TX_IDX;
alter index APPLSYS.WF_ITEMS_PK REBUILD PARTITION WF_ITEM1 TABLESPACE APPS_TS_TX_IDX
*
ERROR at line 1:
ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index
Index Definition:
CREATE UNIQUE INDEX "APPLSYS"."WF_ITEMS_PK" ON "APPLSYS"."WF_ITEMS" ("ITEM_TYPE", "ITEM_KEY")
PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING
STORAGE(INITIAL 40960 NEXT 1048576
PCTINCREASE 0 FREELISTS 32 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "*APPLSYSX*" LOCAL
(PARTITION "WF_ITEM1"
PCTFREE 10 INITRANS 11 MAXTRANS 255
STORAGE(INITIAL 40960 NEXT 1048576
PCTINCREASE 0 FREELISTS 32 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPLSYSX" LOGGING
( SUBPARTITION "SYS_SUBP4861"
TABLESPACE "*APPS_TS_TX_IDX*",
......
......
......
Thanks for your help.
Joy