---该列无数据
alter table kri_entity_hist modify CREATED_DATE TIMESTAMP(6);
---如果所改的列有数据: ORA-01439:column to be modified must be empty to change datetype
1.
alter table
tableName drop
constraints not_null_cons_cno;
alter table kri_entity_hist modify CREATED_DATE null; --如果该列有非空约束
alter table kri_entity_hist add tmp_col TIMESTAMP(6);-- 添加临时列
update kri_entity_hist set tmp_col = CREATED_DATE ; --将目标字段中数据加入到临时列中
update kri_entity_hist set CREATED_DATE = null; --将目标字段数据清空
alter table kri_entity_hist modify (CREATED_DATE TIMESTAMP(6)); --更改目标字段类型
update kri_entity_hist set CREATED_DATE = tmp_col; --将临时列数据加回到目标字段中
alter table kri_entity_hist modify CREATED_DATE not null;--还原非空约束
alter table kri_entity_hist drop column tmp_col; --清除临时列
commit;
2. 使用oracle 的使用查询创建表的功能创建一个新表包含原来的数据:
create table test as select * from tab_target ;
alter table test modify (col_name number(5));
insert into test select * from tab_target;
然后drop 原表,新表更名为原表:
drop table tab_target;
rename test to tab_target;