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

ORA-02158
2026년 08월 11일 | DBMS Error 가이드

이 글에서 다루는 내용

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

ORA-02158 invalid CREATE INDEX option 는?

ORA-02158 에러는 Oracle 데이터베이스에서 CREATE INDEX 문을 실행할 때 유효하지 않은 옵션이나 잘못된 구문을 사용했을 때 발생합니다. 이 에러는 인덱스 생성 시 Oracle이 허용하지 않는 키워드, 잘못된 파라미터 조합, 또는 해당 Oracle 버전에서 지원되지 않는 옵션을 지정했을 경우 주로 나타납니다. 특히 다른 DBMS(MySQL, PostgreSQL 등)의 인덱스 문법을 그대로 Oracle에 적용하거나, 구버전 Oracle에서 신버전 문법을 사용하려 할 때 자주 발생하는 에러입니다.

주요 발생 원인

1. 지원되지 않거나 잘못된 인덱스 옵션 사용

Oracle의 CREATE INDEX 문법에서 허용되지 않는 키워드나 옵션을 사용하면 이 에러가 발생합니다. 예를 들어, USING BTREE, CONCURRENT, IF NOT EXISTS 같은 다른 데이터베이스에서 사용하는 옵션은 Oracle에서 지원되지 않으며, 이를 그대로 사용하면 ORA-02158이 발생합니다. 특히 다른 DBMS의 DDL 스크립트를 Oracle로 마이그레이션할 때 가장 빈번하게 발생하는 원인입니다.

2. 인덱스 스토리지 옵션(STORAGE 절)의 잘못된 파라미터 지정

CREATE INDEX 문에 STORAGE 절을 사용할 때 유효하지 않은 파라미터 값이나 잘못된 조합을 사용하면 에러가 발생합니다. INITIAL, NEXT, MINEXTENTS, MAXEXTENTS 등의 값이 Oracle 내부 제한을 벗어나거나, 서로 충돌하는 옵션을 함께 사용하는 경우가 이에 해당합니다. 또한 로컬 관리 테이블스페이스(Locally Managed Tablespace)에서는 일부 STORAGE 파라미터가 무시되거나 에러를 일으킬 수 있습니다.

3. 파티션 인덱스 생성 시 잘못된 옵션 조합 사용

파티션 테이블에 인덱스를 생성할 때 LOCAL과 GLOBAL 옵션을 잘못 혼용하거나, 파티션 인덱스에 허용되지 않는 옵션을 함께 지정하면 ORA-02158이 발생합니다. 예를 들어, BITMAP 인덱스와 GLOBAL PARTITIONED 옵션을 특정 조건 없이 함께 사용하거나, 파티션 키와 맞지 않는 컬럼 구성을 지정하는 경우가 대표적입니다. 파티션 인덱스는 옵션 간의 상호 의존성이 높아 문법 오류가 자주 발생하는 영역입니다.

해결 방법

원인 1 해결: 잘못된 인덱스 옵션 제거 및 Oracle 표준 문법 사용

다른 DBMS에서 가져온 스크립트의 경우 Oracle에서 지원하지 않는 키워드를 제거하고, Oracle 표준 문법으로 변환해야 합니다.

-- ❌ 잘못된 예시 (MySQL 문법을 Oracle에 그대로 사용)
CREATE INDEX idx_emp_name ON employees(last_name) USING BTREE;

-- ✅ 올바른 Oracle 문법
CREATE INDEX idx_emp_name ON employees(last_name);

-- ❌ 잘못된 예시 (IF NOT EXISTS는 Oracle에서 미지원 - Oracle 23c 이전)
CREATE INDEX IF NOT EXISTS idx_emp_dept ON employees(department_id);

-- ✅ Oracle 23c 이전 버전에서의 올바른 처리 방법
-- 기존 인덱스 존재 여부 확인 후 생성
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_count
    FROM user_indexes
   WHERE index_name = 'IDX_EMP_DEPT';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE INDEX idx_emp_dept ON employees(department_id)';
    DBMS_OUTPUT.PUT_LINE('인덱스가 성공적으로 생성되었습니다.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('인덱스가 이미 존재합니다.');
  END IF;
END;
/

-- ✅ 비트맵 인덱스 올바른 생성 예시
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- ✅ 함수 기반 인덱스(Function-Based Index) 올바른 생성 예시
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

원인 2 해결: STORAGE 절 파라미터 올바르게 설정

STORAGE 절에서 허용된 파라미터만 사용하고, 값의 범위를 Oracle 내부 제한에 맞게 설정해야 합니다.

-- ❌ 잘못된 STORAGE 옵션 예시
CREATE INDEX idx_orders_date ON orders(order_date)
STORAGE (INITIAL 0 NEXT 0 MINEXTENTS 0);
-- INITIAL, NEXT는 0보다 커야 하며 최소값 제한이 있음

-- ✅ 올바른 STORAGE 절 사용 예시
CREATE INDEX idx_orders_date ON orders(order_date)
TABLESPACE users
STORAGE (
  INITIAL  64K
  NEXT     64K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
);

-- ✅ 로컬 관리 테이블스페이스에서는 STORAGE 절 생략 권장
-- (Locally Managed Tablespace에서는 extent 크기 자동 관리)
CREATE INDEX idx_orders_custid ON orders(customer_id)
TABLESPACE users;

-- ✅ 현재 테이블스페이스 관리 방식 확인 쿼리
SELECT tablespace_name, extent_management, allocation_type
  FROM dba_tablespaces
 WHERE tablespace_name = 'USERS';

원인 3 해결: 파티션 인덱스 옵션 올바르게 지정

파티션 인덱스 생성 시 LOCAL/GLOBAL 옵션과 다른 옵션의 조합을 정확히 이해하고 사용해야 합니다.

-- 파티션 테이블 예시
CREATE TABLE sales (
  sale_id     NUMBER,
  sale_date   DATE,
  region_id   NUMBER,
  amount      NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

-- ✅ 올바른 LOCAL 파티션 인덱스 생성
CREATE INDEX idx_sales_local ON sales(sale_date)
LOCAL;

-- ✅ 올바른 GLOBAL 파티션 인덱스 생성
CREATE INDEX idx_sales_global ON sales(region_id)
GLOBAL PARTITION BY RANGE (region_id) (
  PARTITION p_r1 VALUES LESS THAN (100),
  PARTITION p_r2 VALUES LESS THAN (200),
  PARTITION p_rmax VALUES LESS THAN (MAXVALUE)
);

-- ✅ 비파티션 글로벌 인덱스 (기본값)
CREATE INDEX idx_sales_amount ON sales(amount);

-- ❌ 잘못된 예시: BITMAP + GLOBAL PARTITIONED 조합 오류
-- Oracle에서는 BITMAP 인덱스는 LOCAL 파티션만 지원
-- CREATE BITMAP INDEX idx_sales_bitmap ON sales(region_id) GLOBAL; -- 에러 발생

-- ✅ BITMAP 파티션 인덱스는 반드시 LOCAL로 생성
CREATE BITMAP INDEX idx_sales_region ON sales(region_id)
LOCAL;

-- ✅ 생성된 인덱스 파티션 정보 확인
SELECT index_name, partitioning_type, locality, alignment
  FROM dba_part_indexes
 WHERE table_name = 'SALES';

예방 방법

1. 인덱스 생성 전 Oracle 공식 문서로 문법 검증하기

Oracle 버전별로 지원하는 CREATE INDEX 옵션이 다르기 때문에, 인덱스 생성 스크립트를 작성하기 전에 반드시 해당 Oracle 버전의 공식 SQL Reference 문서를 확인해야 합니다. 특히 타 DBMS에서 마이그레이션하는 경우, 자동화 도구(Oracle SQL Developer Migration, AWS SCT 등)를 활용해 문법 호환성을 사전에 검토하고, 변환 결과를 개발 환경에서 반드시 테스트한 후 운영 환경에 적용하는 프로세스를 수립해야 합니다.

-- Oracle 버전 확인
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

-- 현재 데이터베이스에서 사용 가능한 인덱스 유형 확인
-- (index_type 컬럼으로 B-TREE, BITMAP, FUNCTION-BASED 등 확인 가능)
SELECT index_name, index_type, status, partitioned
  FROM user_indexes
 ORDER BY index_name;

2. DDL 변경 이력 관리 및 테스트 환경 사전 검증 체계 구축

모든 인덱스 DDL 스크립트는 형상 관리 도구(Git 등)로 버전 관리하고, 운영 환경 적용 전 반드시 개발 및 스테이징 환경에서 사전 테스트를 수행해야 합니다. DBMS_METADATA 패키지를 활용하면 기존에 생성된 인덱스의 DDL을 추출하여 참고 템플릿으로 활용할 수 있으며, 이를 통해 검증된 문법 패턴을 재사용하는 것이 안전합니다.

-- 기존 인덱스의 DDL 추출 (참고 템플릿으로 활용)
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_NAME', 'HR') AS index_ddl
  FROM dual;

-- 특정 테이블의 모든 인덱스 DDL 일괄 추출
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) AS index_ddl
  FROM dba_indexes
 WHERE table_name  = 'EMPLOYEES'
   AND table_owner = 'HR';