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.
1. 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 scaleWe 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.
1. 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> set autocommit on;
SQL> alter table hr.employees add (salary_1 number(6));
Table altered.2. 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.
3. Empty the Old Column
We null out the old column.
SQL> update hr.employees set salary = null;
107 rows updated.4. 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.5. Restore Data
We null out the old column.
SQL> update hr.employees set salary = salary_1;
107 rows updated.6. 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.7. 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.