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

最新推荐文章于 2024-04-09 16:27:49 发布

转载最新推荐文章于 2024-04-09 16:27:49 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kerrycode/p/6960933.html

本文介绍了解决在Oracle数据库中修改NUMBER类型字段精度或刻度时出现的ORA-01440错误的两种方法。一种是通过创建备份表、添加临时字段并迁移数据来实现；另一种则是先备份数据，清空表后再修改表结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在修改表字段的NUMBER类型的精度或刻度时，你可能会遇到ORA-01440: column to be modified must be empty to decrease precision or scale，下面介绍一下，如何处理这个问题。测试案例如下：

   SQL> drop table test;
    
   Table dropped.
    
   SQL>create table test(product_id  number,  price number(38,1));
    
   Table created.
    
   SQL> insert into test
    
     2  select 1001, 18.2 from dual union all
    
     3  select 1002, 38.5 from dual union all
    
     4  select 1003, 34.8 from dual union all
    
     5  select 1004, 87.4 from dual;
    
   4 rows created.
    
   SQL> commit;
    
   Commit complete.
    
   SQL> alter table test modify price number(38,2);
    
   alter table test modify price number(38,2)
    
                           *
    
   ERROR at line 1:
    
   ORA-01440: column to be modified must be empty to decrease precision or scale
    
   SQL>

如上所示，当我们修改字段price的NUMBEr类型的刻度时，就会遇到ORA-01440: column to be modified must be empty to decrease precision or scale，解决这个问题的方法有两种

方案1：

1：首先对该表做逻辑备份，当然如果你确定没有什么问题，也可以忽略此步骤。作为DBA，一般都应该有强烈的风险意识。

   SQL> create table test_20170608_bak
     2  as
     3  select * from test;
    
   Table created.
    
   SQL> 
  

2：增加一个临时字段用来复制旧字段数据

   SQL> alter table test add price_tmp number(38,1);
    
   Table altered.
    
   SQL> update test set price_tmp = price;
    
   4 rows updated.
    
   SQL> commit;
    
   Commit complete.

3：修改字段price的刻度（Scale）值

   SQL> update test set price = null;
    
   4 rows updated.
    
   SQL> commit;
    
   Commit complete.
    
   SQL> alter table test modify price number(38,2); 
    
   Table altered.

4：将数据从字段price_tmp更新回price字段

   SQL> update test set price = price_tmp;
    
   4 rows updated.
    
   SQL> commit;
    
   Commit complete.
    
   SQL>

5：删除临时字段price_tmp

   SQL> alter table test drop column price_tmp;
    
   Table altered.

方案2：

另外一种方法就是备份数据，然后删除全部数据，然后修改表结构，最后将数据更新回去。如下所示：

1：备份原表数据

   SQL> create table test_bak
     2  as
     3  select * from test;
    
   Table created.
    
   SQL>
  

2：清理删除原表数据

   SQL> truncate table test;
    
   Table truncated.

3：修改表资源的精度或标度

   SQL> alter table test modify price number(38,3);
    
   Table altered.
    
   SQL>

4：将数据还原回去

   SQL> insert into test
     2  select * from test_bak;
    
   4 rows created.
    
   SQL> commit;
    
   Commit complete.
    
   SQL> select * from test;
    
   PRODUCT_ID      PRICE
   ---------- ----------
         1001       18.2
         1002       38.5
         1003       34.8
         1004       87.4
    
   SQL> 
  

另外，需要注意的是，这两者方法都必须确保操作时，没有业务或应用程序操作该表，否则会有数据一致性问题。

转载于:https://www.cnblogs.com/kerrycode/p/6960933.html