How to Resolve ORA-01440: column to be modified must be empty to decrease precision or scale

ORA-01440

ORA-01440 means that you can't decrease the number precision of the column values directly by modifying the definition of the column.

Let's see a case that can reproduce the error by modifying the precision of a column directly.

Check the Column's Precision

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

As you can see, the column SALARY is defined with NUMBER data type as NUMBER(8,2). Which means the precision is 8 and the scale is 2 for decimal.

Now we want to decrease the precision of the column to 6, so we modify the column's definition directly like the following.

SQL> alter table hr.employees modify(salary number(6));
alter table hr.employees modify(salary number(6))
                                *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

We saw ORA-01440: column to be modified must be empty to decrease precision or scale. So how to decrease the precision of a NUMBER column? Let's continue.

Solution to ORA-01440

The solution is to make a new column defined with the new precision NUMBER(6) to replace the original column.

Add a New Column

We'd like to use the new column to backup the target column, furthermore, we should add it with a different name and the new precision.

SQL> alter table hr.employees add (salary_1 number(6));

Table altered.

Backup Data

We backup all data from the original column to the new one.

SQL> update hr.employees set salary_1 = salary;

107 rows updated.

Please note that, NOT NULL constraint can be applied on the new column after data is imported if there's any.

Empty the Old Column

We null out the old column.

SQL> update hr.employees set salary = null;

107 rows updated.

Modify the Old Column

We can safely alter the old column into the new definition when the column is empty.

SQL> alter table hr.employees modify(salary number(6));

Table altered.

This DDL implicitly commits all above changes.

Restore Data

We null out the old column.

SQL> update hr.employees set salary = salary_1;

107 rows updated.

Drop the Backup Column

Since we have got our data back, we can drop the old column now.

SQL> alter table hr.employees drop column salary_1;

Table altered.

This DDL implicitly commits all above changes.

Check the Column's Precision

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(6)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

We have decreased the precision of the column. The best thing is that we keep the column order of the table.

In fact, the approach that we introduced above is similar with modifying a column from VARCHAR2 into CLOB.