ORA-01439: Three Ways to Change the Data Type of a Non-Empty Column in Oracle

Changing a column’s data type while adjusting table structure is an operation both ops engineers and developers run into. But Oracle imposes a hard restriction on columns that already hold data—modifying it directly raises an error:

1	ORA-01439: 要更改数据类型, 则要修改的列必须为空

(The error text in an English environment: ORA-01439: column to be modified must be empty to change datatype)

The Symptom and When It Triggers

The trigger condition is clear-cut: running ALTER TABLE ... MODIFY column_name new_type against a table that already has data, where the column currently holds non-NULL values. If the column is empty (all NULL, or the table has no rows at all), the MODIFY succeeds outright; if the column has data, Oracle refuses the change.

1
2
3

-- the example_column column of table example_table already has data
ALTER TABLE example_table MODIFY example_column VARCHAR2(100);
-- ORA-01439: 要更改数据类型, 则要修改的列必须为空

Root Cause Analysis

Oracle doesn’t allow changing a type while data is in place, and the reason lies at the storage layer: a column’s data type determines its physical storage format inside data blocks (NUMBER’s variable-length encoding, VARCHAR2’s length bytes, DATE’s 7-byte fixed length). When the type changes, every existing row in the block would have to be rewritten in the new format. Oracle doesn’t do that rewrite for you automatically (the conversion could lose precision or produce illegal values), so it hands the decision straight back to the DBA: either guarantee the column is empty, or move the data yourself.

One exception is worth knowing: compatible widening changes don’t raise ORA-01439 and can be MODIFYed directly—for example VARCHAR2(50) to VARCHAR2(100) (widening only, never shrinking), or NUMBER(5) to NUMBER(10) (increased precision). The storage format stays the same or is backward compatible, so Oracle allows it. What gets blocked are cross-type conversions (VARCHAR2 → NUMBER, DATE → VARCHAR2) and shrinking changes.

Solutions (Choose by Scenario)

Method 1: Empty the Column, Then Modify Directly

Applies when: the data in this column can be cleared (rewritten afterwards, or not needed in the first place).

-- clear the column data
UPDATE example_table SET example_column = NULL;
COMMIT;

-- change the data type (the column is empty, so MODIFY is allowed)
ALTER TABLE example_table MODIFY example_column VARCHAR2(100);

The first UPDATE nulls every value in the column, satisfying Oracle’s “column must be empty” precondition; the second MODIFY then completes the type change while the column is empty. Before executing, make absolutely sure the clearing won’t affect other systems or applications that depend on this column’s data—and remember to COMMIT the UPDATE, otherwise the nulled state is invisible outside your own session.

Method 2: Bridge with a New Column, Copy the Data Over

Applies when: the data cannot be lost; the routine change scenario. The most common choice.

-- 1. create a column with the new type
ALTER TABLE example_table ADD temp_column VARCHAR2(100);

-- 2. convert the data and copy it into the new column
UPDATE example_table SET temp_column = CAST(example_column AS VARCHAR2(100));
COMMIT;

-- 3. drop the old column
ALTER TABLE example_table DROP COLUMN example_column;

-- 4. rename the new column to the old name
ALTER TABLE example_table RENAME COLUMN temp_column TO example_column;

CAST performs the type conversion during the copy, and incompatible values all surface right here (a failed conversion errors out immediately—dirty data is never silently written). That’s exactly why rehearsing in a test environment is valuable: which rows will blow up becomes fully visible during the UPDATE phase.

This chain adds, changes, and drops things on the table, so two cautions apply: with large data volumes, the table-lock time of the UPDATE and DROP COLUMN is non-trivial—back up before you operate; and DROP COLUMN is irreversible, so confirm step 2’s data is complete before proceeding (cross-check the row count with SELECT COUNT(*) FROM example_table WHERE temp_column IS NOT NULL).

Method 3: Build a New Table and Migrate Everything

Applies when: the table structure itself needs a major overhaul, the column change involves multiple interlinked columns, or this kind of change happens frequently.

-- 1. create the new table with the correct structure
CREATE TABLE example_table_new (
  id INT,
  example_column VARCHAR2(100)
  -- fill in the remaining columns per the new structure
);

-- 2. migrate the data (CAST performs the conversion)
INSERT INTO example_table_new (id, example_column)
SELECT id, CAST(example_column AS VARCHAR2(100))
FROM example_table;
COMMIT;

-- 3. drop the old table and rename the new one
DROP TABLE example_table;
ALTER TABLE example_table_new RENAME TO example_table;

The new-table approach’s benefit is straightening out the structure in one pass (indexes, constraints, and defaults all get redefined cleanly in the CREATE TABLE); the cost is that INSERT ... SELECT copies the entire table, and the old table’s indexes, constraints, triggers, and grants do not come over automatically—each must be rebuilt on the new table. Business writes during the migration need separate arrangements (a downtime window, or dual writes/incremental catch-up), making this suitable for go-live only after strict testing.

Method 4: DBMS_REDEFINITION Online Redefinition (Big Tables, Minimal Downtime)

Applies when: large tables, minimal downtime required, and you’re on Oracle Enterprise Edition (DBMS_REDEFINITION is an EE feature). The idea is the same as Method 3—build a new table and move the data—but the database maintains incremental synchronization in the background, and a brief table lock is only needed at the instant of switchover (not run live; procedure source: Oracle Database Administrator’s Guide):

-- 0. preflight: the table must have a primary key, or allow redefinition by ROWID
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EXAMPLE_TABLE');

-- 1. create the interim table (target structure)
CREATE TABLE example_table_new AS SELECT * FROM example_table WHERE 1=0;
-- and change example_column to the target type (the interim table is empty at this point, so it can be modified directly)

-- 2. start redefinition, converting the type in the column mapping
EXEC DBMS_REDEFINITION.START_REDEF_TABLE( -
  uname       => 'SCOTT', -
  orig_table  => 'EXAMPLE_TABLE', -
  int_table   => 'EXAMPLE_TABLE_NEW', -
  col_mapping => 'example_column CAST(example_column AS VARCHAR2(100))');

-- 3. SYNC multiple times mid-flight to catch up increments and shorten the final switchover window
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE( -
  'SCOTT', 'EXAMPLE_TABLE', 'EXAMPLE_TABLE_NEW');

-- 4. finish the swap: the original table gets the new structure, and the business only feels one brief lock
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE( -
  'SCOTT', 'EXAMPLE_TABLE', 'EXAMPLE_TABLE_NEW');

-- 5. cleanup: the interim table now holds the old structure—confirm, then DROP
DROP TABLE example_table_new;

If a conversion error surfaces mid-flight, ABORT_REDEF_TABLE rolls everything back and the original table stays untouched—that’s the extra escape hatch this method has over Methods 2/3. Indexes, constraints, and triggers can be copied over with COPY_TABLE_DEPENDENTS; grants still need to be added manually.

Verifying the Fix

All three main methods finish with the same verification routine:

-- 1. confirm the column type is now the target type
DESC example_table
-- or query the data dictionary:
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'EXAMPLE_TABLE' AND column_name = 'EXAMPLE_COLUMN';

-- 2. confirm the row count matches the pre-change count
SELECT COUNT(*) FROM example_table;

-- 3. spot-check key rows for correct values (especially CAST-converted columns)
SELECT * FROM example_table WHERE ROWNUM <= 10;

Verification points: data_type/data_length match the target, the row count adds up, and spot-checked values show no garbled text or truncation. Only when all three pass is the change truly closed out.

Prevention Going Forward

Leave headroom in columns likely to grow when you design tables (VARCHAR2 length, NUMBER precision). Compatible widening changes don’t raise ORA-01439, so future capacity growth is painless.
Manage schema changes through scripts (Flyway/Liquibase, or at least a versioned SQL directory), and rehearse every change script in full against a test database first—that’s where CAST failures on specific rows get exposed early.
For big-table changes, estimate the table-lock duration in advance and pick a low-traffic window; Methods 2/3 involve DROP, so confirm the backup is available before executing.

Things to Watch Out For

The root of ORA-01439 is “non-empty columns can’t be type-changed directly”; all three methods essentially work around that restriction rather than lifting it.
Back up before you touch anything; the most common failure of type conversion is incompatible values, and the CAST stage is where they all surface—running it in a test environment first is discipline, not a suggestion.
Selection rule of thumb: data can be cleared → Method 1 (least invasive); routine change → Method 2 (most common); structural overhaul → Method 3 (straightens everything at once); Enterprise Edition big table needing minimal downtime → Method 4 (online redefinition).
In Method 2, remember that dropping the column also takes its indexes and constraints with it; if the new column needs indexes/constraints, rebuild them. Method 3 has the same caveat, and on a larger scale.
The SQL in this post uses generic Oracle syntax (applicable to 11g and above) and was not run against a real Oracle instance (Oracle can’t be containerized for quick verification); syntax follows the Oracle Database SQL Language Reference. Method 4’s procedure comes from the online redefinition chapter of the Oracle Database Administrator’s Guide—likewise a documentation source, not run live.