Skip to content
March 18, 2011 / doganay

ORA-01439: COLUMN TO BE MODIFIED MUST BE EMPTY TO CHANGE DATATYPE

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi,

Let’s assume that we want to change a column type(number to varchar2 for example).
Our table is table1.

create table table1(a number);
insert into table1 select 333 from dual;
commit;
--1 row(s) inserted
alter table table1 modify a varchar2(20);
ORA-01439: column to be modified must be empty to change datatype

So, you must empty table(or column) and then alter table..

let’s copy our table data, that’s less confusing..

create table table2 as select * from table1;
truncate table table1;
alter table table1 modify a varchar2(20);
insert into table1 select * from table2;
commit;
--1 row(s) inserted

try and pray 🙂

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: