Oracle ORA-01792 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01792: Maximum Number of Columns in a Table or View is 1000

ORA-01792 is a hard limit enforced by Oracle Database that prevents any single table or view from containing more than 1,000 columns. This restriction is baked into Oracle's internal architecture and cannot be overridden by any initialization parameter or system setting. It commonly surfaces during large-scale data migrations, poorly normalized schema designs, or automated ETL processes that dynamically generate wide tables.

Top 3 Causes

1. Denormalized "Wide Table" Design

Developers sometimes place hundreds or thousands of attributes into a single table instead of normalizing the schema — for example, spreading monthly sales figures across columns (JAN_SALES, FEB_SALES, ..., DEC_SALES_YEAR_NPLUS1) rather than storing them as rows.

-- BAD: Attempting to create a table with more than 1000 columns
CREATE TABLE SALES_WIDE (
    SALES_ID     NUMBER PRIMARY KEY,
    JAN_2020     NUMBER,
    FEB_2020     NUMBER,
    -- ... hundreds more columns ...
    DEC_2023     NUMBER   -- triggers ORA-01792 past column 1000
);

-- GOOD: Store time-series data as rows instead
CREATE TABLE SALES_NORMALIZED (
    SALES_ID     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    SALES_DATE   DATE          NOT NULL,
    AMOUNT       NUMBER(15, 2) NOT NULL,
    REGION       VARCHAR2(100)
);

2. Legacy System Migration Without Schema Redesign

Migrating from non-Oracle databases (e.g., SQL Server, Sybase) that allow more than 1,000 columns per table — or simply copying DDL scripts verbatim — can trigger this error immediately during the import phase.

-- Check which tables are approaching or exceeding the 1000-column limit
SELECT TABLE_NAME,
       COUNT(*)  AS COLUMN_COUNT,
       CASE
           WHEN COUNT(*) >= 1000 THEN 'EXCEEDS LIMIT'
           WHEN COUNT(*) >=  900 THEN 'CRITICAL'
           WHEN COUNT(*) >=  800 THEN 'WARNING'
           ELSE 'OK'
       END       AS STATUS
FROM   DBA_TAB_COLUMNS
WHERE  OWNER = 'YOUR_SCHEMA'
GROUP  BY TABLE_NAME
HAVING COUNT(*) >= 800
ORDER  BY COLUMN_COUNT DESC;

3. Dynamic Table Creation via CTAS or Automated Scripts

ETL pipelines or reporting tools that auto-generate tables using CREATE TABLE AS SELECT across many joined source tables can silently accumulate columns until the limit is hit.

-- Dangerous pattern: joining many tables with SELECT *
-- This can easily exceed 1000 columns
CREATE TABLE REPORT_SNAPSHOT AS
SELECT *          -- pulls ALL columns from every joined table
FROM   TABLE_A a
JOIN   TABLE_B b ON a.ID = b.ID
JOIN   TABLE_C c ON a.ID = c.ID
-- ... more joins ...
;

-- Safe pattern: explicitly list only the columns you need
CREATE TABLE REPORT_SNAPSHOT AS
SELECT a.ID,
       a.CUSTOMER_NAME,
       b.ORDER_DATE,
       b.ORDER_TOTAL,
       c.REGION_NAME
FROM   TABLE_A a
JOIN   TABLE_B b ON a.ID = b.ID
JOIN   TABLE_C c ON a.ID = c.ID;

Quick Fix Solutions

Option A — Vertical Table Splitting: Divide the wide table into multiple related tables sharing the same primary key.

-- Split a 1200-column table into two groups linked by PK
CREATE TABLE ENTITY_CORE (
    ENTITY_ID  NUMBER PRIMARY KEY,
    COL_001    VARCHAR2(100),
    -- ... up to ~499 more columns
    COL_500    VARCHAR2(100)
);

CREATE TABLE ENTITY_EXT (
    ENTITY_ID  NUMBER PRIMARY KEY,
    COL_501    VARCHAR2(100),
    -- ... remaining columns
    COL_999    VARCHAR2(100),
    CONSTRAINT FK_EXT FOREIGN KEY (ENTITY_ID)
        REFERENCES ENTITY_CORE(ENTITY_ID)
);

Option B — EAV (Entity-Attribute-Value) Model: Best when most column values are NULL (sparse data).

CREATE TABLE ENTITY_MASTER (
    ENTITY_ID   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ENTITY_NAME VARCHAR2(200) NOT NULL
);

CREATE TABLE ENTITY_ATTR (
    ATTR_ID     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ENTITY_ID   NUMBER        NOT NULL,
    ATTR_NAME   VARCHAR2(100) NOT NULL,
    ATTR_VALUE  VARCHAR2(4000),
    CONSTRAINT FK_EA_ENT  FOREIGN KEY (ENTITY_ID) REFERENCES ENTITY_MASTER(ENTITY_ID),
    CONSTRAINT UQ_EA      UNIQUE (ENTITY_ID, ATTR_NAME)
);

CREATE INDEX IDX_EA_ENT  ON ENTITY_ATTR(ENTITY_ID);

Option C — JSON Column (Oracle 12c+): Store flexible, schema-less attributes in a single JSON column.

CREATE TABLE PRODUCT (
    PRODUCT_ID   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(200) NOT NULL,
    ATTRIBUTES   CLOB,
    CONSTRAINT CHK_JSON CHECK (ATTRIBUTES IS JSON)
);

-- Query a specific JSON attribute
SELECT PRODUCT_ID,
       JSON_VALUE(ATTRIBUTES, '$.color')  AS COLOR,
       JSON_VALUE(ATTRIBUTES, '$.weight') AS WEIGHT
FROM   PRODUCT
WHERE  JSON_VALUE(ATTRIBUTES, '$.color') = 'blue';

Prevention Tips

Enforce a column-count gate in your DDL deployment pipeline. Run a pre-check query against DBA_TAB_COLUMNS before any ALTER TABLE ADD COLUMN or table-creation script is executed in production. Alert the team when any table reaches 800 columns.
Mandate schema normalization reviews. Require a DBA or data architect sign-off for any table design exceeding 100 columns. Treat wide tables as a red flag in code and design reviews, and prefer row-oriented or JSON-based designs for dynamic or sparse attribute sets.

Related Oracle Errors

ORA-01795 — maximum number of expressions in a list is 1000: Triggered when an IN clause contains more than 1,000 values — shares the same "1000 limit" theme as ORA-01792.
ORA-00910 — specified length too long for its datatype: Often encountered alongside wide-table design issues when column sizes are also misconfigured.
ORA-01401 — inserted value too large for column: Can co-occur when migrating data into a restructured table with tighter column definitions.

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