Oracle ORA-14060 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14060: Data Type or Length of a Table Partitioning Column May Not Be Changed

ORA-14060 is thrown by Oracle when you attempt to use ALTER TABLE ... MODIFY to change the data type or length of a column that serves as a partitioning key. Oracle enforces this restriction to maintain the integrity of partition boundary values, which are tightly coupled to the original column definition. Simply put, once a column is designated as a partitioning key, its data type and length are frozen for the lifetime of the partitioned table.

Top 3 Causes

1. Directly Modifying the Data Type of a Partitioning Key Column

The most common cause is running a straightforward ALTER TABLE MODIFY against a partitioned table without checking whether the target column is a partitioning key.

-- This will trigger ORA-14060 if order_date is the partitioning key
ALTER TABLE orders MODIFY (order_date TIMESTAMP);
-- ORA-14060: data type or length of a table partitioning column may not be changed

-- First, check if the column is a partitioning key
SELECT name AS table_name, column_name, column_position
FROM dba_part_key_columns
WHERE owner       = 'YOUR_SCHEMA'
  AND name        = 'ORDERS'
  AND column_name = 'ORDER_DATE';

2. Attempting to Extend or Shrink the Column Length

Even if the data type stays the same, changing the length of a partitioning key column — whether increasing or decreasing it — is not allowed.

-- Trying to extend VARCHAR2 length on a LIST partition key → ORA-14060
ALTER TABLE sales MODIFY (region_code VARCHAR2(100));

-- Verify the partitioning key columns for the table
SELECT kc.column_name, tc.data_type, tc.data_length
FROM dba_part_key_columns kc
JOIN dba_tab_columns tc
  ON  tc.owner       = kc.owner
  AND tc.table_name  = kc.name
  AND tc.column_name = kc.column_name
WHERE kc.owner = 'YOUR_SCHEMA'
  AND kc.name  = 'SALES';

3. Migration Tools or Auto-DDL Scripts Ignoring Partition Metadata

Tools like Flyway, Liquibase, or Hibernate ddl-auto generate ALTER TABLE MODIFY statements without awareness of partition key constraints. Scripts that work fine on regular tables will fail immediately on partitioned tables.

-- A migration script that doesn't check partition key status
-- might generate something like:
ALTER TABLE customer_orders MODIFY (cust_region VARCHAR2(200));
-- This fails with ORA-14060 if cust_region is the partition key

-- Safe pre-check before running any migration:
SELECT COUNT(*) AS is_partition_key
FROM dba_part_key_columns
WHERE owner       = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
  AND name        = 'CUSTOMER_ORDERS'
  AND column_name = 'CUST_REGION';
-- If result > 0, the column cannot be directly modified

Quick Fix Solutions

Option 1 — Recreate the Table (Recommended)

The safest and most reliable fix is to create a new partitioned table with the desired column definition and migrate data across.

-- Step 1: Create new table with corrected column definition
CREATE TABLE orders_new (
    order_id    NUMBER,
    order_date  DATE,
    region_code VARCHAR2(100),   -- updated length
    amount      NUMBER(15, 2)
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

-- Step 2: Migrate data efficiently
INSERT /*+ APPEND PARALLEL(orders_new, 4) */
INTO orders_new
SELECT order_id, order_date, region_code, amount
FROM orders;
COMMIT;

-- Step 3: Swap table names
RENAME orders     TO orders_old;
RENAME orders_new TO orders;

-- Step 4: Drop old table after validation
-- DROP TABLE orders_old PURGE;

Option 2 — Use EXCHANGE PARTITION for Non-Key Columns

If the column you truly need to modify is not the partitioning key, use EXCHANGE PARTITION to work on a regular table temporarily.

-- Exchange partition to a regular table, modify, then swap back
CREATE TABLE orders_temp AS SELECT * FROM orders WHERE 1=0;

ALTER TABLE orders
    EXCHANGE PARTITION p_2023
    WITH TABLE orders_temp WITHOUT VALIDATION;

-- Modify non-partitioning columns freely on the regular table
ALTER TABLE orders_temp MODIFY (amount NUMBER(18, 2));

ALTER TABLE orders
    EXCHANGE PARTITION p_2023
    WITH TABLE orders_temp WITHOUT VALIDATION;

DROP TABLE orders_temp;

Prevention Tips

Design partitioning keys with room to grow. Choose generous data types from the start (e.g., TIMESTAMP instead of DATE, or VARCHAR2(200) instead of VARCHAR2(50)). Changing a partitioning key column later is extremely disruptive.
Add a partition-key pre-check to every DDL script. Before any column modification, query DBA_PART_KEY_COLUMNS to confirm the target column is not a partitioning key. Make this a mandatory step in your CI/CD pipeline or migration framework.

-- Reusable pre-check snippet
SELECT column_name
FROM dba_part_key_columns
WHERE owner       = 'YOUR_SCHEMA'
  AND name        = UPPER('&table_name')
  AND column_name = UPPER('&column_name');
-- Zero rows = safe to modify; any rows = ORA-14060 will occur

Related Errors

Error Code	Description
ORA-14001	Generic DDL not permitted on a partitioned table
ORA-14074	Partition bound conflicts with existing bound values
ORA-14016	Partition attribute modification not allowed
ORA-00054	Resource busy — may appear during table swap operations

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

DEV Community