1.将该列设置为null,再修改其类型(这样会丢失数据)
2.最简单的方法,新增加一张表。(但这样可能造成新表与原来的表约束不一致)
假设你的表名为 tab_target
create table test as select * from tab_target where 1=2;
alter table test modify (col_name number(5));
insert into test select * from tab_target;
drop table tab_target;
rename test to tab_target;
eg.
create table ZJ_MDR_PROPERTIES_new as select * from ZJ_MDR_PROPERTIES where 1=2;
alter table ZJ_MDR_PROPERTIES_new modify (yp number(12,4));
alter table ZJ_MDR_PROPERTIES_new modify (pv number(12,4));
alter table ZJ_MDR_PROPERTIES_new modify (FUNNELVISC number(12,4));
insert into ZJ_MDR_PROPERTIES_new select * from ZJ_MDR_PROPERTIES;
drop table ZJ_MDR_PROPERTIES;
rename ZJ_MDR_PROPERTIES_new to ZJ_MDR_PROPERTIES;
3.good idea:将要更改类型的字段名改名以备份,然后添加一个与要更改类型的字段名同名的字段(原字段已经改名),然后更新数据,最后删除改名备份的字段(最佳)
/*修改原字段名*/ alter table 表名 rename column 字段名 to 字段名1;
/*添加一个和原字段同名的字段*/ alter table 表名 add 字段名 VARCHAR2(30);
/*
将原来的数据更新到新字段中
这是要注意,一定要显示进行数据类型转换(不同于MSSQL)
*/ update 表名 set 字段名=cast(字段名1 as varchar2(30));
/*删除原来的备份字段*/ alter table 表名 drop column 字段名1
eg.(下面处理方式,大致思路一样)
alter table ZJ_MDR_PROPERTIES add (FUNNELVISC_new number(12,4));//增加新字段update ZJ_MDR_PROPERTIES set FUNNELVISC_new = FUNNELVISC
alter table ZJ_MDR_PROPERTIES rename column FUNNELVISC to FUNNELVISC_old20130122
alter table ZJ_MDR_PROPERTIES rename column FUNNELVISC_new to FUNNELVISC
alter table ZJ_MDR_PROPERTIES drop column FUNNELVISC_old20130122