DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02158 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02158: Invalid CREATE INDEX Option — Causes & Fixes

ORA-02158 is thrown by Oracle when the CREATE INDEX statement contains an unrecognized or unsupported option. This typically happens when syntax from another DBMS (such as MySQL or PostgreSQL) is applied directly to Oracle, or when an invalid combination of index options is used. Understanding the root cause quickly is essential to keeping your DDL scripts clean and your deployments smooth.

Top 3 Causes

1. Using Non-Oracle Keywords in CREATE INDEX

Oracle does not support keywords like USING BTREE, CONCURRENT, or IF NOT EXISTS (prior to Oracle 23c) that are common in other databases. Migrating scripts from MySQL or PostgreSQL without syntax conversion is the most frequent trigger for this error.

-- ❌ Invalid: MySQL-style syntax
CREATE INDEX idx_emp_name ON employees(last_name) USING BTREE;

-- ✅ Valid Oracle syntax
CREATE INDEX idx_emp_name ON employees(last_name);

-- ✅ Checking if index exists before creating (pre-23c workaround)
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_count
    FROM user_indexes
   WHERE index_name = 'IDX_EMP_NAME';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE INDEX idx_emp_name ON employees(last_name)';
    DBMS_OUTPUT.PUT_LINE('Index created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Index already exists.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Invalid STORAGE Clause Parameters

Specifying out-of-range or incompatible values in the STORAGE clause—such as setting INITIAL or NEXT to zero—will trigger ORA-02158. Additionally, using STORAGE parameters in a Locally Managed Tablespace can cause conflicts.

-- ❌ Invalid: INITIAL and NEXT cannot be 0
CREATE INDEX idx_orders_date ON orders(order_date)
STORAGE (INITIAL 0 NEXT 0 MINEXTENTS 0);

-- ✅ Valid STORAGE clause
CREATE INDEX idx_orders_date ON orders(order_date)
TABLESPACE users
STORAGE (
  INITIAL     64K
  NEXT        64K
  MINEXTENTS  1
  MAXEXTENTS  UNLIMITED
  PCTINCREASE 0
);

-- ✅ Check tablespace management type before using STORAGE
SELECT tablespace_name, extent_management, allocation_type
  FROM dba_tablespaces
 WHERE tablespace_name = 'USERS';
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Partition Index Option Combinations

Mixing incompatible options when creating partitioned indexes—such as combining BITMAP with GLOBAL PARTITIONED—is a common cause of ORA-02158. Oracle only supports LOCAL partitioning for bitmap indexes.

-- ❌ Invalid: BITMAP indexes cannot be GLOBAL PARTITIONED
CREATE BITMAP INDEX idx_sales_region ON sales(region_id) GLOBAL;

-- ✅ Valid: BITMAP indexes must use LOCAL partitioning
CREATE BITMAP INDEX idx_sales_region ON sales(region_id)
LOCAL;

-- ✅ Valid: GLOBAL partitioned index for B-tree index
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)
);

-- ✅ Verify partition index attributes
SELECT index_name, partitioning_type, locality
  FROM dba_part_indexes
 WHERE table_name = 'SALES';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
Migrating from MySQL/PostgreSQL Remove USING BTREE, CONCURRENT, IF NOT EXISTS
STORAGE clause error Use valid positive values; omit STORAGE in LMT tablespaces
Bitmap + Global partition Change to LOCAL partitioning
Unknown option Reference Oracle SQL Reference for your exact version 
-- Extract DDL of an existing index to use as a verified template
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_NAME', 'HR')
  FROM dual;

-- Check your Oracle version to confirm supported options
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always validate syntax against the correct Oracle version documentation.
Oracle SQL syntax evolves across versions (11g, 12c, 19c, 21c, 23c). Before writing any DDL, confirm that the options you intend to use are supported in your target version. Use Oracle's official SQL Language Reference as your primary source.

2. Test all DDL in a non-production environment first.
Leverage DBMS_METADATA.GET_DDL to extract proven index DDL from existing objects as reference templates. Establish a mandatory dev/staging validation step in your deployment pipeline to catch syntax errors before they reach production.

-- Bulk extract all index DDLs for a given table
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) AS ddl
  FROM dba_indexes
 WHERE table_name  = 'EMPLOYEES'
   AND table_owner = 'HR';
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

  • ORA-00907 — Missing right parenthesis; often accompanies syntax errors in CREATE INDEX.
  • ORA-01408 — Column already indexed; triggers when creating a duplicate index.
  • ORA-14016 — Partition index constraint violation; can be confused with ORA-02158 in partitioned index scenarios.
  • ORA-00955 — Object name already exists; common when re-running index creation scripts.

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