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

Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)