Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Move tablespace for a partitioned index

user8686720Apr 22 2013 — edited Apr 22 2013
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 20 2013
Added on Apr 22 2013
7 comments
2,720 views