Move IOT related table, index and overflow segment to another tablespace

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

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         CLASSIFICATION_STORE

3. Move the table instead rebuild the index

SQL> alter table EDWREP.CLASSIFICATION_STORE move tablespace APPS_TS_OATM_MIG;
Table altered.

4. Overflow segment also can not be moved directly by referencing the Overflow table name

SQL> alter table EDWREP.SYS_IOT_OVER_57745 move tablespace APPS_TS_OATM_MIG;
alter table EDWREP.SYS_IOT_OVER_57745 move tablespace APPS_TS_OATM_MIG
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

5. Find out the IOT_NAME for the overflow table

SQL> select iot_name from dba_tables where table_name = 'SYS_IOT_OVER_57745';
IOT_NAME
--------------------
RESERVATION_STORE

6. Using the IOT_NAME with ‘alter table .. move overflow ..’ to move overflow segment

SQL> alter table edwrep.RESERVATION_STORE move overflow tablespace APPS_TS_OATM_MIG;
Table altered.