Skip to content

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

  • by

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 the 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 and parallelly, no matter it's a normal table or an IOT.

SQL> alter table hr.countries move tablespace users online;

Table altered.

Leave a Reply

Your email address will not be published.