ORA-01439: column to be modified must be empty to change datatype
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 NUBER(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
