Tag Archives: Modify the column data type

ORA-01439: column to be modified must be empty to change datatype

How to Change Column Type in Oracle Database Safely

I want to change the table NAME datatype from Number to varchar character type. When i fired it is giving the following error as:
ORA-01439: column to be modified must be empty to change datatype

SQL> desc test1;
 Name Null? Type
 ------------ -------- ----------------
 NAME NUMBER(10)
 ID NOT NULL NUMBER(11)
SQL> alter table test1 modify name varchar2(10);
alter table test1 modify name varchar2(10)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Solution:
1. Create a backup table for the table so that we will remove data from existing table.

SQL> create table test1_bkp as select * from test1;
Table created.

SQL> select * from test1_bkp;
NAME ID
---- -------
1 1
2 2

2. Empty the original table

SQL> delete from test1;
2 rows deleted.

SQL> commit;
Commit complete.

3. Modify the column data type

SQL> alter table test1 modify name varchar2(10);
Table altered.

4. Insert back the existing data into table.

SQL> insert into test1 select * from test1_bkp;
2 rows created.
SQL> commit;
Commit complete.

5. Test the table is working fine

SQL> select * from test1;
NAME    ID
-----  ------
1      1
2      2