DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-14086 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14086: A Partitioned Index May Not Be Rebuilt as a Whole

ORA-14086 is thrown when a DBA attempts to rebuild a partitioned index without specifying an individual partition or subpartition. Oracle's architecture requires each index partition to be rebuilt independently, as every partition maintains its own segment. Understanding this constraint is essential for anyone managing large partitioned table environments.

Top 3 Causes

1. Running REBUILD Without Specifying a Partition

The most common cause is simply executing a standard REBUILD command on a partitioned index, the same way you would for a non-partitioned index.

-- This will FAIL with ORA-14086
ALTER INDEX hr.emp_salary_idx REBUILD;

-- Correct approach: specify the partition
ALTER INDEX hr.emp_salary_idx REBUILD PARTITION p2024_q1;
ALTER INDEX hr.emp_salary_idx REBUILD PARTITION p2024_q2;
Enter fullscreen mode Exit fullscreen mode

2. Bulk Index Maintenance Scripts Not Filtering Partitioned Indexes

Automated maintenance scripts that iterate over all indexes without distinguishing between partitioned and non-partitioned indexes will hit this error.

-- Check if an index is partitioned before rebuilding
SELECT owner,
       index_name,
       partitioned,
       status
FROM   dba_indexes
WHERE  owner = 'HR'
  AND  partitioned = 'NO'   -- Only rebuild non-partitioned indexes this way
  AND  status = 'UNUSABLE';
Enter fullscreen mode Exit fullscreen mode

3. Recovering UNUSABLE Indexes After Partition DDL Operations

After operations like SPLIT PARTITION or EXCHANGE PARTITION, associated indexes can become UNUSABLE. Attempting a full index rebuild instead of a partition-level rebuild triggers ORA-14086.

-- Find UNUSABLE partitioned index partitions
SELECT index_owner,
       index_name,
       partition_name,
       status
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
ORDER BY index_owner, index_name;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use the following script to automatically rebuild all UNUSABLE partitioned index partitions:

BEGIN
    FOR r IN (
        SELECT index_owner,
               index_name,
               partition_name
        FROM   dba_ind_partitions
        WHERE  status = 'UNUSABLE'
          AND  index_owner NOT IN ('SYS', 'SYSTEM')
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE
                'ALTER INDEX ' || r.index_owner || '.' || r.index_name ||
                ' REBUILD PARTITION ' || r.partition_name || ' ONLINE';
            DBMS_OUTPUT.PUT_LINE('OK: ' || r.index_name ||
                                 ' - ' || r.partition_name);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('FAILED: ' || r.index_name ||
                                     ' - ' || SQLERRM);
        END;
    END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

For composite-partitioned indexes, also handle subpartitions:

-- Rebuild UNUSABLE subpartitions
BEGIN
    FOR r IN (
        SELECT index_owner, index_name, subpartition_name
        FROM   dba_ind_subpartitions
        WHERE  status = 'UNUSABLE'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER INDEX ' || r.index_owner || '.' || r.index_name ||
            ' REBUILD SUBPARTITION ' || r.subpartition_name || ' ONLINE';
    END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always filter by PARTITIONED column in maintenance scripts. Query DBA_INDEXES.PARTITIONED first and branch your logic accordingly — use partition-level REBUILD for 'YES' and standard REBUILD for 'NO'.

  • Schedule an index health check job using DBMS_SCHEDULER to periodically query DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS for UNUSABLE status, especially after any partition DDL operations. Early detection prevents application-level errors like ORA-01502 from surfacing in production.

Related Errors

Error Code Description
ORA-01502 Index or index partition is in UNUSABLE state — often follows a failed rebuild
ORA-14048 Invalid combination in partition maintenance operation
ORA-14074 Improper operations on local partitioned index partitions

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)