ORA-28650
Tried to move and rebuild an index to another tablespace, but it failed with ORA-28650.
SQL> alter index hr.country_c_id_pk rebuild tablespace users online;
alter index hr.country_c_id_pk rebuild tablespace users online
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
ORA-28650 means that the index you want to rebuild is essentially an index-organized table (IOT), you cannot treat it like a normal index, instead, you should treat it as a table.
Let's check the index.
SQL> column index_type format a15;
SQL> column tablespace_name format a15;
SQL> column table_owner format a15;
SQL> column table_name format a15;
SQL> select index_type, tablespace_name, table_owner, table_name from all_indexes where owner = 'HR' and index_name = 'COUNTRY_C_ID_PK';
INDEX_TYPE TABLESPACE_NAME TABLE_OWNER TABLE_NAME
--------------- --------------- --------------- ---------------
IOT - TOP EXAMPLE HR COUNTRIES
As we can see, the index type is index-organized.
Solution
To move an index-organized index, you should move its source table instead.
SQL> alter table hr.countries move tablespace users;
Table altered.
Then we check any changed information of the index.
SQL> select index_type, tablespace_name, table_owner, table_name from all_indexes where owner = 'HR' and index_name = 'COUNTRY_C_ID_PK';
INDEX_TYPE TABLESPACE_NAME TABLE_OWNER TABLE_NAME
--------------- --------------- --------------- ---------------
IOT - TOP USERS HR COUNTRIES
As you can see, the index was moved to USER tablespace, too.
Move Online
For release 12.2 and later releases, we can move the table online or parallelly, no matter it's a normal table or an IOT.
SQL> alter table hr.countries move tablespace users online;
Table altered.
We have solved ORA-28650.