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