I faced wiht that error message while i was trying to run ALTER TABLE MODIFY statement
My database version is 11.2.0.1, I tried to run ALTER TABLE MODIFY statement on my table than I hit this error message.
Let me try to explain cause of this error and solution steps.
1. Create demo table:
CREATE TABLE cities
( city_id number(10),
city_name varchar2(150) );
2. Insert data into table:
INSERT into cities
(city_id , city_name)
values (34, ‘ISTANBUL’);
3. Modify table:
ALTER TABLE cities
MODIFY city_id varchar2(15);
Hitting error:
ORA-01439: column to be modified must be empty to change datatype
You can only modify the datatype of a column whose values are all NULL.
Solution:
UPDATE cities
SET city_id = NULL;
or
DELETE FROM cities
or
Truncate table cities
Now you can run your alter command.
Bir Cevap Yazın