ORA-01502 Index or Partition Unusable - Complete Resolution Guide

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

Error Overview

Error Text: ORA-01502: index 'SCHEMA.INDEX_NAME' or partition of such index is in unusable state

The ORA-01502 error occurs when Oracle attempts to use an index that has been marked as UNUSABLE. This typically happens after certain DDL operations, failed maintenance activities, or explicit marking. The error prevents queries and DML operations that require the index until it’s rebuilt.

Common Causes

1. DDL Operations

• ALTER TABLE MOVE without rebuilding indexes
• ALTER TABLE SPLIT PARTITION
• ALTER TABLE MOVE PARTITION
• ALTER INDEX REBUILD PARTITION failure

2. Direct-Path Operations

• SQL*Loader with DIRECT=TRUE
• INSERT /*+ APPEND */ operations
• CREATE TABLE AS SELECT with NOLOGGING

3. Explicit Marking

• ALTER INDEX index_name UNUSABLE
• Index maintenance procedures

4. Partition Operations

• Adding/splitting/merging partitions
• Partition exchange operations
• Truncating partitions

5. Failed Operations

• Insufficient space during rebuild
• Interrupted maintenance jobs
• System crashes during DDL

Diagnostic Queries

Find All Unusable Indexes

-- Find unusable non-partitioned indexes
SELECT owner, index_name, table_name, status, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE'
ORDER BY owner, table_name, index_name;

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

-- Find unusable index subpartitions
SELECT index_owner, index_name, partition_name, subpartition_name, status
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE'
ORDER BY index_owner, index_name, partition_name;

Check Specific Index Status

-- Detailed index information
SELECT
  i.owner,
  i.index_name,
  i.table_name,
  i.index_type,
  i.status,
  i.partitioned,
  i.tablespace_name,
  i.last_analyzed,
  i.num_rows
FROM dba_indexes i
WHERE i.index_name = 'INDEX_NAME'
  AND i.owner = 'SCHEMA_NAME';

-- For partitioned index, check all partitions
SELECT
  partition_name,
  status,
  tablespace_name,
  num_rows,
  last_analyzed
FROM dba_ind_partitions
WHERE index_name = 'INDEX_NAME'
  AND index_owner = 'SCHEMA_NAME'
ORDER BY partition_position;

Identify Impact

-- Find tables affected by unusable indexes
SELECT DISTINCT
  i.owner,
  i.table_name,
  COUNT(*) as unusable_index_count
FROM dba_indexes i
WHERE i.status = 'UNUSABLE'
GROUP BY i.owner, i.table_name
UNION ALL
SELECT DISTINCT
  ip.index_owner,
  i.table_name,
  COUNT(*)
FROM dba_ind_partitions ip
JOIN dba_indexes i ON ip.index_owner = i.owner AND ip.index_name = i.index_name
WHERE ip.status = 'UNUSABLE'
GROUP BY ip.index_owner, i.table_name;

Resolution Steps

1. Rebuild Non-Partitioned Index

-- Basic rebuild
ALTER INDEX schema_name.index_name REBUILD;

-- Rebuild with options
ALTER INDEX schema_name.index_name REBUILD
  TABLESPACE new_tablespace
  PARALLEL 4
  NOLOGGING;

-- Online rebuild (minimizes locking)
ALTER INDEX schema_name.index_name REBUILD ONLINE;

-- After NOLOGGING rebuild, consider backup
ALTER INDEX schema_name.index_name LOGGING;

2. Rebuild Partitioned Index

-- Rebuild single partition
ALTER INDEX schema_name.index_name
REBUILD PARTITION partition_name;

-- Rebuild partition online
ALTER INDEX schema_name.index_name
REBUILD PARTITION partition_name ONLINE;

-- Rebuild all unusable partitions (generate script)
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name ||
       ' REBUILD PARTITION ' || partition_name || ' ONLINE;'
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
  AND index_owner = 'SCHEMA_NAME';

3. Rebuild Subpartitioned Index

-- Rebuild single subpartition
ALTER INDEX schema_name.index_name
REBUILD SUBPARTITION subpartition_name;

-- Generate script for all unusable subpartitions
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name ||
       ' REBUILD SUBPARTITION ' || subpartition_name || ';'
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE'
  AND index_owner = 'SCHEMA_NAME';

4. Rebuild All Unusable Indexes (Script Generator)

-- Generate rebuild commands for all unusable indexes
SET SERVEROUTPUT ON
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  -- Non-partitioned indexes
  FOR rec IN (
    SELECT owner, index_name
    FROM dba_indexes
    WHERE status = 'UNUSABLE'
  ) LOOP
    v_sql := 'ALTER INDEX ' || rec.owner || '.' || rec.index_name || ' REBUILD ONLINE';
    DBMS_OUTPUT.PUT_LINE(v_sql || ';');
  END LOOP;

  -- Partitioned indexes
  FOR rec IN (
    SELECT index_owner, index_name, partition_name
    FROM dba_ind_partitions
    WHERE status = 'UNUSABLE'
  ) LOOP
    v_sql := 'ALTER INDEX ' || rec.index_owner || '.' || rec.index_name ||
             ' REBUILD PARTITION ' || rec.partition_name || ' ONLINE';
    DBMS_OUTPUT.PUT_LINE(v_sql || ';');
  END LOOP;
END;
/

5. Using DBMS_REPAIR for Corrupt Indexes

-- If index is corrupt, drop and recreate
-- First, get the index DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', 'INDEX_NAME', 'SCHEMA_NAME')
FROM dual;

-- Drop the index
DROP INDEX schema_name.index_name;

-- Recreate using the DDL obtained above
CREATE INDEX schema_name.index_name ON schema_name.table_name(columns)
TABLESPACE index_tablespace
ONLINE;

Handling Specific Scenarios

After ALTER TABLE MOVE

-- Move table to new tablespace
ALTER TABLE schema_name.table_name MOVE TABLESPACE new_tablespace;

-- All indexes are now UNUSABLE - rebuild them
-- For each index:
ALTER INDEX schema_name.index_name REBUILD;

-- Or use a single statement to rebuild all (11g+)
-- This rebuilds all indexes on the table:
BEGIN
  FOR rec IN (
    SELECT index_name
    FROM dba_indexes
    WHERE table_name = 'TABLE_NAME'
      AND owner = 'SCHEMA_NAME'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX SCHEMA_NAME.' || rec.index_name || ' REBUILD';
  END LOOP;
END;
/

After Partition Operations

-- After SPLIT PARTITION
-- Global indexes become unusable, local indexes for new partitions need rebuild

-- Rebuild global indexes
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;'
FROM dba_indexes
WHERE table_name = 'PARTITIONED_TABLE'
  AND owner = 'SCHEMA_NAME'
  AND partitioned = 'NO'
  AND status = 'UNUSABLE';

-- To avoid this, use UPDATE INDEXES clause
ALTER TABLE schema_name.table_name
SPLIT PARTITION old_part AT (value)
INTO (PARTITION new_part1, PARTITION new_part2)
UPDATE INDEXES;  -- Maintains index validity

For Direct-Path Load Operations

-- Before direct-path load, you can skip index maintenance
ALTER SESSION SET skip_unusable_indexes = TRUE;

-- Load data (indexes become unusable)
-- SQL*Loader or INSERT /*+ APPEND */

-- After load, rebuild indexes
ALTER INDEX schema_name.index_name REBUILD;

-- Reset session parameter
ALTER SESSION SET skip_unusable_indexes = FALSE;

Prevention Strategies

1. Use UPDATE INDEXES Clause

-- Partition maintenance with index preservation
ALTER TABLE schema_name.table_name
MOVE PARTITION partition_name
TABLESPACE new_tablespace
UPDATE INDEXES;

ALTER TABLE schema_name.table_name
SPLIT PARTITION old_partition AT (split_value)
INTO (PARTITION part1, PARTITION part2)
UPDATE INDEXES;

2. Use ONLINE Operations

-- Online table move (12c+)
ALTER TABLE schema_name.table_name MOVE ONLINE;

-- Online partition move
ALTER TABLE schema_name.table_name
MOVE PARTITION partition_name ONLINE;

3. Monitoring Script

-- Create monitoring job
CREATE OR REPLACE PROCEDURE check_unusable_indexes AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_indexes
  WHERE status = 'UNUSABLE';

  SELECT COUNT(*) + v_count INTO v_count
  FROM dba_ind_partitions
  WHERE status = 'UNUSABLE';

  IF v_count > 0 THEN
    -- Send alert or log
    INSERT INTO alert_log (alert_date, alert_type, alert_message)
    VALUES (SYSDATE, 'UNUSABLE_INDEX',
            v_count || ' unusable indexes detected');
    COMMIT;
  END IF;
END;
/

-- Schedule daily check
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'CHECK_UNUSABLE_INDEXES',
    job_type => 'STORED_PROCEDURE',
    job_action => 'check_unusable_indexes',
    repeat_interval => 'FREQ=DAILY; BYHOUR=6',
    enabled => TRUE
  );
END;
/

4. Session Parameter for Bulk Operations

-- Allow DML to skip unusable indexes (use carefully)
ALTER SESSION SET skip_unusable_indexes = TRUE;

-- Perform bulk operations
-- ...

-- Rebuild indexes afterward
-- ...

-- Reset
ALTER SESSION SET skip_unusable_indexes = FALSE;

Troubleshooting

Insufficient Space for Rebuild

-- Check available space
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space
WHERE tablespace_name = 'INDEX_TABLESPACE'
GROUP BY tablespace_name;

-- Estimate index size
SELECT ROUND(SUM(bytes)/1024/1024, 2) as current_size_mb
FROM dba_segments
WHERE segment_name = 'INDEX_NAME'
  AND owner = 'SCHEMA_NAME';

-- If space is tight, rebuild to different tablespace
ALTER INDEX schema_name.index_name
REBUILD TABLESPACE different_tablespace;

Long-Running Rebuild

-- Monitor rebuild progress
SELECT sid, serial#, opname, target, sofar, totalwork,
       ROUND(sofar/NULLIF(totalwork,0)*100, 2) as pct_complete,
       time_remaining
FROM v$session_longops
WHERE opname LIKE '%Index%'
  AND sofar < totalwork;

-- Use parallel for faster rebuild
ALTER INDEX schema_name.index_name REBUILD PARALLEL 8;

-- Reset parallelism after rebuild
ALTER INDEX schema_name.index_name NOPARALLEL;

Cannot Rebuild Due to Locks

-- Check for locks on the table
SELECT
  l.session_id,
  s.serial#,
  s.username,
  l.lock_type,
  l.mode_held,
  l.mode_requested,
  l.blocking_others
FROM dba_lock l
JOIN v$session s ON l.session_id = s.sid
WHERE l.lock_type IN ('DML', 'DDL')
  AND l.owner = 'SCHEMA_NAME';

-- Use ONLINE rebuild to minimize lock time
ALTER INDEX schema_name.index_name REBUILD ONLINE;

Related Errors

• ORA-01578 - Oracle data block corrupted
• ORA-01653 - Unable to extend table
• ORA-01654 - Unable to extend index
• ORA-14086 - Partition maintenance operation

Best Practices Summary

1. Always rebuild indexes after ALTER TABLE MOVE
2. Use UPDATE INDEXES clause for partition operations
3. Prefer ONLINE rebuilds to minimize downtime
4. Monitor for unusable indexes regularly
5. Use PARALLEL for large index rebuilds, then reset to NOPARALLEL
6. Consider skip_unusable_indexes for bulk operations, but rebuild promptly
7. Test partition maintenance procedures in non-production first