DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02211 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02211: Invalid Value for PCTFREE or PCTUSED

ORA-02211 is thrown by Oracle when you specify an out-of-range or logically inconsistent value for the PCTFREE or PCTUSED storage parameters in a CREATE or ALTER statement. PCTFREE must be between 0 and 99, PCTUSED must be between 1 and 99, and the sum of both must not reach or exceed 100. Getting this error usually means a typo, a bad script variable, or a misunderstanding of how block space management works.

Top 3 Causes

1. Value Out of Allowed Range

Passing a negative number, zero for PCTUSED, or any value ≥ 100 triggers ORA-02211 immediately.

-- BAD: PCTFREE of 100 is not allowed
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE
)
PCTFREE 100
PCTUSED 40;
-- ORA-02211: invalid value for PCTFREE or PCTUSED

-- GOOD: values within valid range
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE
)
PCTFREE 20
PCTUSED 40;
Enter fullscreen mode Exit fullscreen mode

2. PCTFREE + PCTUSED Sum Equals or Exceeds 100

Oracle enforces that PCTFREE + PCTUSED < 100 to prevent overlapping space management logic within a data block.

-- BAD: 60 + 50 = 110, exceeds the limit
ALTER TABLE customers
    PCTFREE 60
    PCTUSED 50;
-- ORA-02211: invalid value for PCTFREE or PCTUSED

-- GOOD: 30 + 50 = 80, within the limit
ALTER TABLE customers
    PCTFREE 30
    PCTUSED 50;
Enter fullscreen mode Exit fullscreen mode

3. Non-Integer or Uninitialized Variable Passed via Dynamic SQL

When DDL is generated programmatically (shell scripts, Python, PL/SQL), an uninitialized variable or a string value can slip into the PCTFREE/PCTUSED position, causing the error.

-- Safe dynamic DDL with validation in PL/SQL
DECLARE
    v_pctfree NUMBER := 20;
    v_pctused NUMBER := 40;
    v_sql     VARCHAR2(500);
BEGIN
    -- Guard clauses before executing DDL
    IF v_pctfree NOT BETWEEN 0 AND 99 THEN
        RAISE_APPLICATION_ERROR(-20001, 'PCTFREE must be 0-99');
    END IF;
    IF v_pctused NOT BETWEEN 1 AND 99 THEN
        RAISE_APPLICATION_ERROR(-20002, 'PCTUSED must be 1-99');
    END IF;
    IF (v_pctfree + v_pctused) >= 100 THEN
        RAISE_APPLICATION_ERROR(-20003, 'PCTFREE + PCTUSED must be < 100');
    END IF;

    v_sql := 'CREATE TABLE safe_table (id NUMBER) '
          || 'PCTFREE ' || v_pctfree
          || ' PCTUSED ' || v_pctused;
    EXECUTE IMMEDIATE v_sql;
    DBMS_OUTPUT.PUT_LINE('Table created successfully.');
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Check current storage parameters before altering an object: 
SELECT table_name, pct_free, pct_used
FROM   user_tables
WHERE  table_name = 'YOUR_TABLE_NAME';

SELECT index_name, pct_free, status
FROM   user_indexes
WHERE  table_name = 'YOUR_TABLE_NAME';
Enter fullscreen mode Exit fullscreen mode
  1. Apply safe default values that work for most OLTP workloads: 
-- Rebuild an index with a safe PCTFREE
-- Note: PCTUSED does not apply to indexes
ALTER INDEX idx_orders_date
    REBUILD
    PCTFREE 10;

-- Alter a table with safe, validated values
ALTER TABLE orders
    PCTFREE 20
    PCTUSED 40;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Define organizational DDL templates with pre-approved storage values (e.g., PCTFREE 20 PCTUSED 40 for OLTP, PCTFREE 5 PCTUSED 60 for data warehouse tables). Enforce these through peer code reviews and approved script libraries.

  • Add a DDL validation step in your CI/CD pipeline using tools like Liquibase or Flyway combined with a custom pre-check script. Automatically reject any DDL where PCTFREE or PCTUSED falls outside the valid range or their sum reaches 100. This catches the error before it ever reaches production.

Related Errors

Error Code Description
ORA-02207 Invalid value for INITRANS or MAXTRANS
ORA-02143 Invalid STORAGE option
ORA-01735 Invalid ALTER TABLE option
ORA-14024 Invalid storage parameter in partition DDL

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