Rebuilding Oracle Partitioned Index- Solving “ORA-14086: a partitioned index may not be rebuilt as a whole”

It is known that Oracle has two types of partitioned Indexes: Local and Global Partitioned Index. I will only cover some of the specifications that explain their differences. However, according to the VLDB and Partitioning Guide release 21c, “Partitioning is possible on both regular (heap organized) tables and index-organized tables, except for those containing LONG or LONG RAW columns. You can create nonpartitioned global indexes, range or hash partitioned global indexes, and local indexes on partitioned tables.”. Furthermore, while creating a local index, the database constructs the index, which is specific for each partition. At the same time, the global index covers all partitions.
Nevertheless, this post is about rebuilding both of them. When I first had to rebuild an Oracle partitioned index, the first thing that I was notified about it was that we had an invalid index in our system. Still, I could not find them until I used the correct SQL query to search for the information and then created a command to rebuild it. Therefore, follow below the first attempt followed by the valid command to solve the “ORA-14086: a partitioned index may not be rebuilt as a whole” you might be seeing.

— This is the query in that I could not find the name of the index because I thought it was in a typical user I had experienced other problems before:

SET LINESIZE 5000;
SET PAGESIZE 5000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||' online ;' from dba_IND_PARTITIONS where INDEX_OWNER='&INDEX_OWNER' AND INDEX_NAME='&INDEX_NAME';

— Therefore, I decided to query it, and then I could find the proper names:

SQL> SELECT INDEX_NAME, PARTITION_NAME from dba_IND_PARTITIONS where STATUS='UNUSABLE';

TECH_KD_PK                                                                                                           TECH_EVNT_20230228
TECH_ID_IX                                                                                                    TECH_20230228

— By having the names, it was easy to create a command to generate the output:

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

SQL> SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
2    3    4    5    6    7    8    9  ' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
10   11   12  WHERE STATUS='UNUSABLE'
13  UNION
14  SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
15  INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE'; 16   17   18   19

ALTER INDEX TECH_OWNER.TECH_KD_PK REBUILD PARTITION TECH_EVNT_20230228 TABLESPACE INDEX_TS;
ALTER INDEX TECH_OWNER.TECH_ID_IX  REBUILD PARTITION TECH_20230228 TABLESPACE INDEX_TS;

— Then I just copied the output above, and in a few minutes, the problem no longer existed:

SQL> ALTER INDEX TECH_OWNER.TECH_KD_PK REBUILD PARTITION TECH_EVNT_20230228 TABLESPACE INDEX_TS;
ALTER INDEX TECH_OWNER.TECH_ID_IX  REBUILD PARTITION TECH_20230228 TABLESPACE INDEX_TS;

brunors

*The views expressed here are my own and do not represent those of my employer.*

Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering,  all driven by a deep passion for transforming data into strategic business value.