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

Posted on Edited on In Views: Disqus: Symbols count in article: 825 Reading time ≈ 2 mins.

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

這篇介紹如何解決ORA-01440: column to be modified must be empty to decrease precision or scale。

Problem

1
2
問題:會發生在ALTER TABLE table_name MODIFY "Salary" NUMBER(3);
由NUMBER大改成小的時候出現ORA-01440: column to be modified must be empty to decrease precision or scale。

Step by Step

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
假設table name:Empolyee
Step1:先備份Empolyee TABLE
$ create table Empolyee_bak as select * from Empolyee;

Step2:增加一個欄位Salary_tmp來備份Salary欄位
$ ALTER TABLE Empolyee add Salary_tmp NUMBER(3);

Step3:將Salary欄位資料複製至Salary_tmp
$ update Empolyee set Salary_tmp = Salary;

Step4:將Salary欄位清空並修改欄位大小
$ update Empolyee set Salary = null;
$ ALTER TABLE Empolyee modify Salary NUMBER(3);

Step5:將Salary_tmp欄位資料複製回Salary
$ update Empolyee set Salary = Salary_tmp;

Step6:刪除Salary_tmp欄位
$ alter table Empolyee drop column Salary_tmp;

備註Step4:將Salary欄位清空時，Salary必須要允許allow NULL