ORA-01440: column to be modified must be empty to decrease precision or scale
Example to generate the error with TEST1 table
--Created table test1
SQL> create table test1 (id number(1));
Table created.
--insert data
SQL> insert into test1 values (1);
1 row created.
--Modified the table with increase scale
SQL> alter table test1 modify id number(2);
Table altered.
--insert data
SQL> insert into test1 values(2);
1 row created.
Getting error while modified the column datatype in decrease length scale
SQL> alter table test1 modify id number(1);
alter table test1 modify id number(1)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
Solution
1. Add a first temporary column with data type used to modified in upper statement.
SQL> alter table test1 add id_temp NUMBER(1);
Table altered.
2. Move the data from original column to first temporary column.
SQL> update test1 set id_temp= id;
2 rows updated.
3. Check the number of rows.
SQL> select count(*) from test1;
COUNT(*)
----------
2
4. Rename the original column to second new temporary column so that we will move temporary column with original name in next step.
SQL> alter table test1 rename column id to id_TEMP1;
Table altered.
Note: We can directly drop id column and rename the first temporary to original column but we add this step to make production data security.(We can skip this step to by dropping first id column)
5. Rename the column which is having original data created first.
SQL> alter table test1 rename column id_temp to id;
Table altered.
6. Drop the extra temporary column.
SQL> alter table test1 drop column id_temp1;
Table altered.
7. Verify the Number of rows is same.
SQL> select count(*) from test1;
COUNT(*)
----------
2
8. Verify the Structure with Describe command.
desc test1;
Name Null? Type
--------- -------- ---------------
ID NUMBER(1)
9. Compile the invalid objects related to that object by executing.
execute utl_recomp.recomp_serial();
