oracle on primary,ERROR at line 1: ORA-28650: Primary index on an IOT cannot be rebuilt

>

When i was trying to move index partitions from one tablespace to another tablespace i am getting below Error.

ERROR at line 1: ORA-28650: Primary index on an IOT cannot be rebuilt

Query :

alter index Test_PK3 rebuild partition P_20111212 tablespace TAB_SP

Note Test_pk3 is an index on IOT table.

Can any one Suggest me to resolve this problem.

>

Sure - look up the exception in the ERROR_MESSAGES doc and follow the instructions.

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e24280.htm#sthref7378

>

ORA-28650: Primary index on an IOT cannot be rebuilt

Cause: An attempt is made to issue alter index rebuild on IOT-TOP

Action: Use Alter table MOVE to reorganize the table(IOT)

>

You cannot rebuild the IOT index directly; you need to 'move' the table to another tablespace and Oracle will rebuild it as part of the move process.

See this short article for how to move your IOT to a new tablespace and solve your problem.

How to move IOT related table, index and overflow segment to another tablespace

http://oracleunix.wordpress.com/2006/10/02/how-to-move-iot-related-table-index-and-overflow-segment-to-another-tablespace/

How to move IOT related table, index and overflow segment to another tablespace

1. Primary index for IOT table can not be rebuilt

SQL> alter index EDWREP.C_S_PK rebuild tablespace APPS_TS_OATM_MIG;

alter index EDWREP.C_S_PK rebuild tablespace APPS_TS_OATM_MIG

*

ERROR at line 1:

ORA-28650: Primary index on an IOT cannot be rebuilt

2. Find out the table name for this IOT table

SQL> select index_type, table_name from dba_indexes where index_name=’C_S_PK’;

INDEX_TYPE TABLE_NAME

————————— ——————————

IOT – TOP CLASSIFICATION_STORE

3. Move the table instead rebuild the index

SQL> alter table EDWREP.CLASSIFICATION_STORE move tablespace APPS_TS_OATM_MIG;

Table altered.