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

修改oracle数据库字段类型，处理ORA-01439错误 (2011-08-26 12:19) 一键转载

标签:   ORA-01439   修改字段   类型  分类：  经验感想

对已经有数据的表修改字段类型时，Oracle提示：ORA-01439: 要更改数据类型, 则要修改的列必须为空。

可以创建新表，灌入原表数据后再改名，或者创建临时字段，替换数据后再删除。

 

测试环境：

1. drop table foo;
   
2. create table foo (col_name varchar2(5));
   
3. insert into foo values('1');
   
4. insert into foo values('12');
   
5. insert into foo values('33445');
   
6. commit;
   
7. create index idx1 on foo (col_name);
   
8. select * from foo;
   
9. desc foo

解决方法

 

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

1. --此处1=2表示只导入格式，因为表达式为假，不会导入数据
2. create table new as select * from foo where 1=2;                                            
   
3. alter table new modify (col_name number(5));
   
4. insert into new select * from foo;
   
5. drop table foo;
   
6. rename new to foo;

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

 

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

1. create table new
   
2. as 
   
3. select [其他的列], 
   
4.  to_number(Char_Col) col_name
   
5. from foo;

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

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

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

 

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

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

<script>window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"16"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];</script>

阅读(512) | 评论(0) | 转发(0) |

0

上一篇：常用的数据类型转换函数

下一篇：oracle修改varchar2为number,

给主人留下些什么吧！~~

评论热议