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;
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)