DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-14001 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14001: only one GLOBAL clause may be specified

ORA-14001 is an Oracle error that occurs when the GLOBAL keyword is specified more than once in a CREATE INDEX or ALTER INDEX statement for a partitioned index. Oracle only allows a single GLOBAL clause per index definition, and any duplication causes the parser to reject the statement immediately. This error is commonly triggered by copy-paste mistakes, script merging issues, or misunderstanding of Oracle's partitioned index syntax.

Top 3 Causes

1. Duplicate GLOBAL Keyword in the Same Statement

The most frequent cause is simply typing or pasting GLOBAL twice in the same DDL statement.

-- Incorrect: GLOBAL appears twice
CREATE INDEX idx_order_date
ON orders (order_date)
GLOBAL GLOBAL PARTITION BY RANGE (order_date)
(
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
-- Raises ORA-14001
Enter fullscreen mode Exit fullscreen mode 
-- Correct: GLOBAL appears only once
CREATE INDEX idx_order_date
ON orders (order_date)
GLOBAL PARTITION BY RANGE (order_date)
(
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
-- Executes successfully
Enter fullscreen mode Exit fullscreen mode

2. Mixing LOCAL and GLOBAL Keywords Together

LOCAL and GLOBAL are mutually exclusive partitioning options. Using both in the same index definition will trigger ORA-14001, as Oracle cannot apply two contradictory partitioning strategies simultaneously.

-- Incorrect: LOCAL and GLOBAL used together
CREATE INDEX idx_emp_dept
ON employees (department_id)
LOCAL GLOBAL PARTITION BY HASH (department_id) PARTITIONS 4;
-- Raises ORA-14001

-- Correct: choose one based on your use case
-- Option A: Local partitioned index (mirrors table partitions)
CREATE INDEX idx_emp_dept_local
ON employees (department_id)
LOCAL;

-- Option B: Global partitioned index (independent partition structure)
CREATE INDEX idx_emp_dept_global
ON employees (department_id)
GLOBAL PARTITION BY HASH (department_id) PARTITIONS 4;
Enter fullscreen mode Exit fullscreen mode

3. Script Merging or Auto-generation Errors

When DDL scripts from different environments are merged, or when code generation tools render the same option twice, duplicate GLOBAL clauses can appear silently.

-- Incorrect: result of a bad script merge
CREATE INDEX idx_sales_region
ON sales (region_id)
GLOBAL                               -- inserted by block A
GLOBAL PARTITION BY LIST (region_id) -- inserted by block B
(
    PARTITION p_kr VALUES ('KR'),
    PARTITION p_us VALUES ('US'),
    PARTITION p_etc VALUES (DEFAULT)
);
-- Raises ORA-14001

-- Correct: single GLOBAL clause
CREATE INDEX idx_sales_region
ON sales (region_id)
GLOBAL PARTITION BY LIST (region_id)
(
    PARTITION p_kr VALUES ('KR'),
    PARTITION p_us VALUES ('US'),
    PARTITION p_etc VALUES (DEFAULT)
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Remove the duplicate GLOBAL keyword — scan your DDL and keep only one GLOBAL clause.
  2. Choose either LOCAL or GLOBAL — never combine them in a single index statement.
  3. Validate after creation — confirm the index structure using the data dictionary: 
-- Verify partitioned index metadata
SELECT index_name, partitioning_type, locality, alignment
FROM dba_part_indexes
WHERE table_name = 'ORDERS';

-- Check partition details
SELECT index_name, partition_name, high_value, status
FROM dba_ind_partitions
WHERE index_name = 'IDX_ORDER_DATE'
ORDER BY partition_position;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Use standardized DDL templates: Maintain version-controlled index creation templates within your team. Modify parameters rather than copy-pasting entire blocks to avoid accidental keyword duplication.
  • Automate syntax validation: Integrate SQL linting tools (e.g., SQLFluff) or peer review checkpoints into your deployment pipeline to catch duplicate keywords before they reach production.

Related Errors

Error Code Description
ORA-14000 only one LOCAL clause may be specified — the LOCAL equivalent of ORA-14001
ORA-14016 Table must be partitioned to use a LOCAL partitioned index
ORA-14036 Partition bound value exceeds the column's data type range

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