修改oracle数据库字段类型，处理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. create table new as select * from foo where 1=2;
   
2. alter table new modify (col_name number(5));
   
3. insert into new select * from foo;
   
4. drop table foo;
   
5. 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;

                                                                                           Oracle重建表(rename)注意事项总结

一、概述
前一段时间，有一个DBA朋友在完成重建表(rename)工作后，第二天早上业务无法正常运行，出现数据无法插入的限制和错误，后来分析才发现，错误的原因是使用rename方式重建表以后，其它引用这个表的外键约束指向没有重新定义到这个重建的新表中，从而导致这些表在插入新数据时，违反数据完整性约束，导致数据无法正常插入。影响了业务大概有1个多小时，真是一次血淋淋的教训啊。使用rename方式重建表是我们日常DBA维护工作中经常使用的一种方法，因为CTAS+rename这种配合方式，非常实用和高效。很多DBA朋友应该也都是用过rename方式重建表，而且重建完成以后也都一切正常，没有引起过问题。但是，我想说的是，使用rename重建表后，具体需要完成哪些扫尾工作你真的清楚吗？？这篇文章主要就是归纳当我们使用rename方式重建表后，需要进行哪些扫尾工作，如果你还不是很清楚，一定要仔细 阅读这篇文章，同时在以后的重建表工作中矫正过来，否则，问题迟早有一天会降临到你的身边！
二、重建表的方式 这里先不谈其它，仅仅说一下重建表的方法。如下 1、为了确保所有表字段、字段类型、长度完全一样，我一般不建议使用CTAS方式来重建表。 2、一般我都是使用下面两种方法中的一个，来抽取表的定义 select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual;
使用PL/SQL developer类似这样的工具，来查看表定义语句
3、重新建一张_old类型的表（根据上面的抽取的表定义），然后使用insert /*+ append */ xx select xxx 方式完成数据的转换 4、最后使用rename方式倒换这两张表的名字

三、重建表注意事项 索引重建：这里最关键的是，重建后索引的名字是否必须和以前的一样，如果需要一样，则必须将当前使用的索引名字先rename，否则创建的时候会出现索引名字已经存在的错误,如下： select 'alter index ' || owner || '.' || index_name || ' rename to ' || substr(index_name, 1, 26) || '_old;' from dba_indexes a where a.table_owner = 'DBMON' AND A.table_name = 'DH_T';
依赖对象重建：一般可以使用如下方式完成 select 'alter '||decode(type,'PACKAGE BODY','PACKAGE',type)||' '||owner||'.'||name||' compile;' from dba_dependencies a where a.referenced_name = 'DH_T' and a.referenced_owner = 'DBMON';
注意： 1、这里重建的只是直接依赖对象，必须考虑那些间接依赖的对象（例如 view1依赖A表，view2依赖view1），查找方法和上面差不多 2、如果这些依赖对象中存在一些私有对象（例如dblink等），我们用DBA用户重新编译是会出现编译错误，对于这种对象，必须以对应对象的所属者才能编译成功。(也可用用10g以后新出现的代理权限来完成这类任务！）针对PL/SQL代码（包、函数、过程等），是否存在私有对象的查找方法，如下： select * from dba_source a where (a.owner, a.name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON') and a.TEXT like '%@%';
针对视图中是否存在私有对象的查找方法，如下(由于是long类型，必须得一个一个查看）： select * from dba_views a where (a.owner, a.view_name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON' and b.type = 'VIEW')
权限重建：可以使用如下语句 select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where table_name = upper('&i_table_name') and owner = upper('&i_owner');
外键重建：对于外键，现在的业务数据逻辑很多都是在应用层来实现，因此表上的外键可能都非常少，因此，导致很多DBA都忘记需要检查和重建这一部分了，从而导致业务出现问题，本章最开始说的故障案例就是因为没有重建外键而引起，因此我们一定要提高警惕。可以使用如下语句查看，哪些表引用了重建表 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name,--被外键引用的约束名 b.table_name --被外键引用的表名 from dba_constraints a, dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = 'FSPARECEIVEBILLTIME' and b.owner='';
物化视图：另外一个非常重要的依赖对象就是物化视图，一般来说，rename表以后，物化视图是不会有问题的，再次刷新时会自动编译，但是这可能会影响优化其选择执行计划，因此，建议手工直接编译这些失效的物化视图，如下 alter MATERIALIZED VIEW DH_T_MV compile; 备注，其实这步已经包含在依赖对象重建部分了，单独拿出来是因为这个依赖对象非常重要，不容有任何意外
物化视图日志：物化视图日志是为了快速刷新准备的，而且从dba_dependencies 这张依赖表中无法查找出来的，但是，对于这个对象，我们一定要保持谨慎和敬畏，因为如果表上存在物化视图日志对象的话，那么这张表无法完成rename（在一个变更的晚上，其它什么都OK了，突然遇到一个这样的问题，还得找开发确认，是非常被动的，整个变更很有可能因为这个无法确认而取消），会直接报错，查找表上的物化视图日志对象方法如下：
select master,log_table from user_mview_logs a where master in ('DH_T');

备注： 我们可能还需要关注表字段类型，那些LOB、long字段都是我们重建表是需要考虑的
还有就是重建表是我们可能都会使用parallel+nologging模式来加快速度，一定要记得在重建完成后将这些属性修改回来。（以前遇到过一个案例，未将parallel属性改回来，导致执行计划选用并行，最终导致资源很快耗尽，CPU100%）
还有一些同步机制，如果同步依赖rowid，由于重建表rowid会该表，可能造成实时同步失败，这些都是我们需要考虑的最后，在工作完成后，检查一下所有对象的有效性是一个不错的方案。（建议在重建前保存快照，重建后与前面的快照比较）上面讲述的都是一些我们最常用的对象，其它一些很少使用的对象这里就不概述了