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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.