DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02143 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02143: Invalid STORAGE Option — Causes, Fixes, and Prevention

ORA-02143 is an Oracle error that occurs when you specify an invalid or unsupported option within the STORAGE clause of a DDL statement such as CREATE TABLE, CREATE INDEX, or ALTER TABLE. Oracle's SQL parser raises this error immediately at parse time, before any physical operation takes place. It is most commonly caused by incorrect parameter values, unsupported keywords, or simple syntax mistakes in the STORAGE clause.

Top 3 Causes

1. Invalid Parameter Values in the STORAGE Clause

Providing out-of-range values — such as a negative number for NEXT, a value exceeding 100 for PCTINCREASE, or an unrecognized size unit — triggers ORA-02143 immediately.

-- This will cause ORA-02143
CREATE TABLE bad_table (
    id   NUMBER,
    name VARCHAR2(100)
)
STORAGE (
    INITIAL     64K
    NEXT       -1M      -- Negative value not allowed
    PCTINCREASE 150     -- Must not exceed 100
);

-- Correct version
CREATE TABLE good_table (
    id   NUMBER,
    name VARCHAR2(100)
)
STORAGE (
    INITIAL     64K
    NEXT        1M
    MINEXTENTS  1
    MAXEXTENTS  UNLIMITED
    PCTINCREASE 0
);
Enter fullscreen mode Exit fullscreen mode

2. Using STORAGE Options Unsupported by the Tablespace Type

Since Oracle 9i, most tablespaces use Locally Managed extent allocation. Some STORAGE parameters are only valid for Dictionary Managed Tablespaces and will cause errors or be silently ignored in Locally Managed Tablespaces. Always check the tablespace type before crafting your DDL.

-- Check tablespace management type before writing DDL
SELECT tablespace_name,
       extent_management,
       allocation_type
FROM   dba_tablespaces
WHERE  tablespace_name = 'USERS';

-- For Locally Managed Tablespaces, minimize or omit STORAGE clause
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE
)
TABLESPACE USERS;  -- Oracle manages extents automatically
Enter fullscreen mode Exit fullscreen mode

3. Syntax Errors in the STORAGE Clause (Commas, Typos, Missing Parentheses)

Unlike most SQL clauses, parameters inside STORAGE () are separated by spaces, not commas. Adding commas between parameters or misspelling a keyword (e.g., INITIALL instead of INITIAL) will cause ORA-02143.

-- WRONG: Commas between STORAGE parameters cause ORA-02143
CREATE TABLE syntax_bad (
    prod_id NUMBER
)
STORAGE (
    INITIAL 64K,    -- comma is NOT allowed here
    NEXT    1M,     -- comma is NOT allowed here
    PCTINCREASE 0
);

-- CORRECT: Parameters separated by spaces only
CREATE TABLE syntax_good (
    prod_id NUMBER
)
STORAGE (
    INITIAL     64K
    NEXT        1M
    MINEXTENTS  1
    MAXEXTENTS  UNLIMITED
    PCTINCREASE 0
);

-- Correct STORAGE clause on an index
CREATE INDEX idx_prod
ON syntax_good (prod_id)
TABLESPACE INDX
STORAGE (
    INITIAL     256K
    NEXT        256K
    PCTINCREASE 0
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Remove commas between STORAGE parameters — use whitespace only.
  2. Check value ranges: PCTINCREASE must be 0–100, MINEXTENTS must be ≥ 1, NEXT must be a positive value.
  3. Verify tablespace type with DBA_TABLESPACES and simplify or omit STORAGE for Locally Managed Tablespaces.
  4. Query existing segments to use proven, working STORAGE settings as a reference: 
-- Use an existing object's storage settings as a reference
SELECT segment_name,
       initial_extent,
       next_extent,
       min_extents,
       max_extents,
       pct_increase
FROM   dba_segments
WHERE  owner        = 'YOUR_SCHEMA'
  AND  segment_name = 'YOUR_TABLE';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Establish a DDL standard template: Create a team-wide approved STORAGE clause template with validated parameter values. This prevents individual developers from introducing invalid values or syntax errors.
  • Always test DDL in a non-production environment first: Run all DDL scripts in a development or QA environment that mirrors production before deployment. Catching ORA-02143 early avoids downtime and rollback headaches in production.

Related Errors

Error Code Description
ORA-02140 Invalid INITIAL storage option value
ORA-02141 Invalid NEXT storage option value
ORA-02142 Missing or invalid STORAGE clause option
ORA-01658 Unable to create INITIAL extent — tablespace space issue

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