ORA-14086: a partitioned index may not be rebuilt as a whole

dbtut November 20, 2019 ORACLE

If you attempt to rebuild an entire index while rebuilding a partitioned index, you may receive an ORA-14086 error.

SQL> alter index index01 rebuild;

 alter index index01 rebuild
 ERROR at line 1:
 ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> alter index index01 rebuild;

alter index index01 rebuild

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

To solve the problem, rebuild all partitions separately.

Rebuild All Partition of a Partitioned Index

You can prepare the rebuild command for all partitions of the index with the following query.

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where index_name='INDEX01';

1	select 'alter index '\|\|index_owner\|\|'.'\|\|index_name\|\|' rebuild partition '\|\|partition_name\|\|';' from dba_ind_partitions where index_name='INDEX01';