修改oracle数据库字段类型，处理ORA-01439错误

最新推荐文章于 2023-03-03 20:32:28 发布

ywb201314

最新推荐文章于 2023-03-03 20:32:28 发布

阅读量510

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

摘要对已经有数据的表修改字段类型时，Oracle提示：ORA-01439: 要更改数据类型, 则要修改的列必须为空。可以创建新表，灌入原表数据后再改名，或者创建临时字段，替换数据后再删除。

目录[-]

测试环境：

 
         drop 
          table 
          foo; 
        
 
         create 
          table 
          foo (col_name varchar2(5)); 
        
 
         insert 
          into 
          foo  
         values 
         ( 
         '1' 
         ); 
        
 
         insert 
          into 
          foo  
         values 
         ( 
         '12' 
         ); 
        
 
         insert 
          into 
          foo  
         values 
         ( 
         '33445' 
         ); 
        
 
         commit 
         ; 
        
 
         create 
          index 
          idx1  
         on 
          foo (col_name); 
        
 
         select 
          *  
         from 
          foo; 
        
 
         desc 
          foo 
        

解决方法

1. 创建新表

根据当前表结构创建新表，修改新表字段，将原数据灌进来，删除旧表，将新表rename为旧表名

1

2

3

4

5

 
         create 
          table 
          new  
         as 
          select 
          *  
         from 
          foo  
         where 
          1=2; 
        
 
         alter 
          table 
          new  
         modify 
          (col_name number(5)); 
        
 
         insert 
          into 
          new  
         select 
          *  
         from 
          foo; 
        
 
         drop 
          table 
          foo; 
        
 
         rename new  
         to 
          foo; 
        

如果原表有索引，触发器、外键等需要新建。

2. 使用CTAS来转换

oracle 的使用查询创建表的功能创建一个新表包含原来的数据，命令如下：

1

2

3

4

5

 
         create 
          table 
          new 
        
         as 
        
         select 
          [其他的列],  
        
         to_number(Char_Col) col_name 
        
         from 
          foo;

然后drop 原表，新表更名为原表：

1 2	`drop` `table` `foo;` `rename new` `to` `foo;`

与方法1一样要注意新建相关对象。

3. 创建临时字段替换

新建一个临时字段，把要修改的字段的内容备份到临时字段后清空原字段，然后再修改类型，之后再把临时字段的内容复制到修改后的字段，最后删除临时字段

1

2

3

4

5

6

7

 
         alter 
          table 
          foo  
         add 
         (tmp_col number(5)); 
        
 
         update 
          foo  
         set 
          tmp_col = to_number(col_name); 
        
 
         update 
          foo  
         set 
          col_name =  
         null 
         ;  
         --此处要小心啊，原数据全没了，一定要保证上一步正确执行 
        

            
        
 
         alter 
          table 
          foo  
         modify 
          (col_name number(5)); 
        
 
         update 
          foo  
         set 
          col_name = tmp_col; 
        
 
         alter 
          table 
          foo  
         drop 
          column 
          tmp_col; 
        
 
         
  
        