Oracle ORA-01502 Error: Causes and Solutions Complete Guide

ORA-01502: Index or Partition of Such Index Is in Unusable State

ORA-01502 occurs when Oracle attempts to use an index (or an index partition) that has been marked as UNUSABLE, causing DML statements or queries to fail. This typically happens after direct path loads, partition DDL operations, or tablespace offline events. Once an index is in an UNUSABLE state, it must be rebuilt before normal operations can resume.

---

Top 3 Causes

1. Direct Path Insert / SQL*Loader with DIRECT=TRUE

When data is loaded using direct path methods, Oracle marks indexes as UNUSABLE to improve load performance.

-- This kind of operation can mark indexes UNUSABLE
INSERT /*+ APPEND */ INTO sales_data
SELECT * FROM sales_staging;
COMMIT;

-- Check for unusable indexes after bulk load
SELECT owner, index_name, table_name, status
FROM dba_indexes
WHERE status = 'UNUSABLE'
  AND owner = 'SALES';

2. Partition DDL Operations Without UPDATE INDEXES

Operations like SPLIT PARTITION, MERGE PARTITION, or MOVE PARTITION can leave indexes in an UNUSABLE state if UPDATE INDEXES is not specified.

-- BAD: This will leave global indexes UNUSABLE
ALTER TABLE sales DROP PARTITION sales_2020;

-- GOOD: Use UPDATE GLOBAL INDEXES to keep indexes valid
ALTER TABLE sales DROP PARTITION sales_2020
  UPDATE GLOBAL INDEXES;

-- GOOD: Split partition and maintain indexes
ALTER TABLE sales SPLIT PARTITION sales_old
  AT (DATE '2023-01-01')
  INTO (PARTITION sales_h1, PARTITION sales_h2)
  UPDATE GLOBAL INDEXES;

3. Tablespace Taken Offline

If the tablespace where an index resides goes offline, Oracle marks those indexes as UNUSABLE. Bringing the tablespace back online does not automatically restore the indexes.

-- Check unusable index partitions
SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
ORDER BY index_owner, index_name;

---

Quick Fix Solutions

Rebuild a Single Index

-- Standard rebuild
ALTER INDEX hr.emp_last_name_idx REBUILD;

-- Online rebuild (recommended for production, minimizes downtime)
ALTER INDEX hr.emp_last_name_idx REBUILD ONLINE;

Rebuild a Specific Index Partition

-- Rebuild a single unusable partition
ALTER INDEX sales.sales_local_idx REBUILD PARTITION sales_2023_q1 ONLINE;

Bulk Rebuild All UNUSABLE Indexes (Automation Script)

BEGIN
  FOR rec IN (
    SELECT owner, index_name
    FROM dba_indexes
    WHERE status = 'UNUSABLE'
      AND owner NOT IN ('SYS', 'SYSTEM')
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.owner || '.' 
        || rec.index_name || ' REBUILD ONLINE';
      DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || rec.owner || '.' || rec.index_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || rec.owner || '.' 
          || rec.index_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

---

Prevention Tips

1. Always use UPDATE INDEXES or UPDATE GLOBAL INDEXES in partition DDL statements. Make this a mandatory coding standard in your team to prevent indexes from going UNUSABLE after partition maintenance.

2. Schedule a nightly monitoring job using DBMS_SCHEDULER to detect and automatically rebuild UNUSABLE indexes after batch processes.
   

-- Register a daily scheduler job to auto-rebuild unusable indexes
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'AUTO_REBUILD_UNUSABLE_IDX',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
      BEGIN
        FOR r IN (
          SELECT owner, index_name FROM dba_indexes
          WHERE status = ''UNUSABLE''
            AND owner NOT IN (''SYS'', ''SYSTEM'')
        ) LOOP
          EXECUTE IMMEDIATE ''ALTER INDEX '' || r.owner 
            || ''.'' || r.index_name || '' REBUILD ONLINE'';
        END LOOP;
      END;',
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE
  );
END;
/

---

Related Errors

• ORA-08102 – Index key not found; indicates index-to-table data inconsistency.
• ORA-14402 – Updating partition key causes a partition change, often seen alongside ORA-01502 in partitioned environments.
• ORA-01578 – Data block corruption, which can cascade into ORA-01502 if the corruption affects index tablespaces.

---

📖 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.