Oracle ORA-01502 오류 원인과 해결 방법 완벽 가이드

ORA-01502
2026년 07월 19일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-01502 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-01502 index or partition of such index is in unusable state 는?

ORA-01502는 Oracle 데이터베이스에서 인덱스 또는 인덱스의 파티션이 UNUSABLE 상태로 변경되어 해당 인덱스를 사용하는 DML(INSERT, UPDATE, DELETE) 또는 쿼리 작업이 실패할 때 발생하는 에러입니다. 인덱스가 UNUSABLE 상태가 되면 Oracle 옵티마이저는 해당 인덱스를 사용할 수 없으며, 강제로 접근 시 이 에러가 발생합니다. 주로 대용량 데이터 로딩 작업, 파티션 관련 DDL 작업, 또는 테이블스페이스 오프라인 이후 인덱스가 무효화될 때 빈번하게 나타납니다.

주요 발생 원인

대용량 데이터 로딩 (Direct Path Insert / SQL*Loader)

직접 경로(Direct Path) 방식으로 데이터를 적재할 때 Oracle은 인덱스 유지 비용을 줄이기 위해 인덱스를 UNUSABLE 상태로 전환합니다. SQLLoader의 DIRECT=TRUE 옵션이나 INSERT /+ APPEND */ 힌트를 사용한 경우에도 동일하게 인덱스가 무효화될 수 있습니다. 대규모 배치 작업 후 REBUILD 없이 후속 DML을 수행하면 바로 ORA-01502가 발생합니다.

파티션 관련 DDL 작업 (SPLIT, MERGE, MOVE, TRUNCATE)

파티션 테이블에서 ALTER TABLE ... SPLIT PARTITION, MERGE PARTITION, MOVE PARTITION, 또는 TRUNCATE PARTITION 작업을 수행하면 해당 파티션의 LOCAL 인덱스가 UNUSABLE 상태로 변경됩니다. Global 인덱스는 전체가 UNUSABLE 상태가 될 수도 있습니다. 이때 UPDATE INDEXES 또는 UPDATE GLOBAL INDEXES 절을 명시하지 않으면 인덱스는 무효 상태로 남습니다.

테이블스페이스 OFFLINE 또는 데이터 파일 손상

인덱스가 저장된 테이블스페이스가 오프라인 상태로 전환되거나 데이터 파일이 손상된 경우, 해당 테이블스페이스에 속한 인덱스는 자동으로 UNUSABLE 상태로 마킹됩니다. 테이블스페이스를 다시 온라인으로 전환하더라도 인덱스 상태가 자동으로 복구되지 않는 경우가 많습니다. 이 경우 수동으로 인덱스를 REBUILD해야 정상 상태로 복원됩니다.

해결 방법

1. UNUSABLE 인덱스 현황 조회

먼저 현재 UNUSABLE 상태인 인덱스를 확인합니다.

-- 일반 인덱스 중 UNUSABLE 상태 조회
SELECT owner, index_name, table_name, status
FROM dba_indexes
WHERE status = 'UNUSABLE'
ORDER BY owner, table_name;

-- 파티션 인덱스 중 UNUSABLE 상태 조회
SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
ORDER BY index_owner, index_name;

-- 서브파티션 인덱스 중 UNUSABLE 상태 조회
SELECT index_owner, index_name, partition_name, subpartition_name, status
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE'
ORDER BY index_owner, index_name;

2. 일반 인덱스 REBUILD

UNUSABLE 상태의 일반 인덱스를 재구성합니다.

-- 단일 인덱스 REBUILD
ALTER INDEX hr.emp_name_idx REBUILD;

-- ONLINE 옵션을 사용하여 서비스 중단 없이 REBUILD (운영 환경 권장)
ALTER INDEX hr.emp_name_idx REBUILD ONLINE;

-- NOLOGGING 옵션으로 빠른 REBUILD (아카이브 로그 최소화)
ALTER INDEX hr.emp_name_idx REBUILD NOLOGGING;

3. 파티션 인덱스 REBUILD

파티션 인덱스의 특정 파티션만 REBUILD하는 방법입니다.

-- 특정 파티션 인덱스 REBUILD
ALTER INDEX sales.sales_idx REBUILD PARTITION sales_2023_q1;

-- 모든 UNUSABLE 파티션을 동적으로 REBUILD하는 스크립트
BEGIN
  FOR rec IN (
    SELECT index_owner, index_name, partition_name
    FROM dba_ind_partitions
    WHERE status = 'UNUSABLE'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_owner || '.' || rec.index_name 
      || ' REBUILD PARTITION ' || rec.partition_name || ' ONLINE';
    DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || rec.index_owner || '.' || rec.index_name 
      || ' PARTITION ' || rec.partition_name);
  END LOOP;
END;
/

4. 파티션 DDL 시 인덱스 자동 유지

파티션 작업 시 인덱스가 UNUSABLE 상태로 전환되지 않도록 합니다.

-- SPLIT PARTITION 시 Global Index 유지
ALTER TABLE sales SPLIT PARTITION sales_old
  AT (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
  INTO (PARTITION sales_2022, PARTITION sales_2023)
  UPDATE GLOBAL INDEXES;

-- TRUNCATE PARTITION 시 인덱스 유지
ALTER TABLE sales TRUNCATE PARTITION sales_2022
  UPDATE INDEXES;

-- MOVE PARTITION 시 인덱스 유지
ALTER TABLE sales MOVE PARTITION sales_2023
  TABLESPACE new_tbs
  UPDATE INDEXES;

5. 일괄 REBUILD 자동화 스크립트

운영 환경에서 사용할 수 있는 전체 UNUSABLE 인덱스 자동 복구 스크립트입니다.

-- 모든 UNUSABLE 일반 인덱스 자동 REBUILD
DECLARE
  v_sql VARCHAR2(500);
BEGIN
  FOR rec IN (
    SELECT owner, index_name
    FROM dba_indexes
    WHERE status = 'UNUSABLE'
      AND owner NOT IN ('SYS', 'SYSTEM')
  ) LOOP
    BEGIN
      v_sql := 'ALTER INDEX ' || rec.owner || '.' || rec.index_name || ' REBUILD ONLINE';
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('SUCCESS: ' || rec.owner || '.' || rec.index_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || rec.owner || '.' || rec.index_name 
          || ' => ' || SQLERRM);
    END;
  END LOOP;
END;
/

예방 방법

파티션 DDL 작업 시 항상 UPDATE INDEXES 또는 UPDATE GLOBAL INDEXES 절 사용

파티션 관련 DDL 작업(SPLIT, MERGE, DROP, TRUNCATE, MOVE 등)을 수행할 때는 반드시 UPDATE INDEXES 또는 UPDATE GLOBAL INDEXES 절을 명시하는 것을 팀 내 표준(코딩 컨벤션)으로 정해야 합니다. 이 절을 사용하면 DDL 완료 후 인덱스가 자동으로 유효 상태를 유지하므로 별도 REBUILD 작업이 필요 없습니다. 단, 이 방식은 DDL 수행 시간이 다소 늘어날 수 있으므로 작업 시간 계획 시 고려해야 합니다.

정기적인 인덱스 상태 모니터링 및 배치 REBUILD 스케줄링

운영 환경에서는 DBMS_SCHEDULER 또는 cron 잡을 활용하여 주기적으로 UNUSABLE 인덱스를 탐지하고 자동으로 REBUILD하는 배치 작업을 구성해야 합니다. 아래 예시와 같이 Oracle Scheduler Job을 등록해 두면 야간 배치 이후 UNUSABLE 상태가 된 인덱스를 자동으로 복구할 수 있습니다.

-- DBMS_SCHEDULER를 이용한 자동 모니터링 JOB 등록 예시
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_REBUILD_UNUSABLE_INDEXES',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
      BEGIN
        FOR rec IN (
          SELECT owner, index_name FROM dba_indexes
          WHERE status = ''UNUSABLE'' AND owner NOT IN (''SYS'',''SYSTEM'')
        ) LOOP
          EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' 
            || rec.index_name || '' REBUILD ONLINE'';
        END LOOP;
      END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Auto rebuild unusable indexes daily at 3AM'
  );
END;
/