alter table test add col1_temp number(5); update test set col1_temp = col1; alter table drop column col1; alter table test rename col1_temp to col1;
1.楼上的修改下,思路是一样的!--char(24)改成number(5): alter table test add col1_temp number(5); update test set col1_temp = to_number(col1); --增加to_number的函数 alter table drop column col1; alter table test rename col1_temp to col1;--number(4)改成number(1): --确认该列的数据只有一位数 alter table test modify column col2 number(1)
怎么把number(5)的改成number(1)的呢?
呵呵,不好意思,少看了一帖,多谢两位。char(24)转成number(5)会报 ORA-01438: value larger than specified precision allowed for this column 我打算先转成number(15),再转成number(5)
报错了 SQL> alter table test modify col2 number(1); alter table test modify col2 number(1) * ERROR at line 1: ORA-01440: column to be modified must be empty to decrease precision or scale
数据量不大的话,先把数据倒出去,然后空表改表结构。然后再倒回去。数据量大,先create table baktablename as select * from tablename ;备份完了,清数据改表结构,然后对数据做一下处理倒回去。不知道 可行不?
update test set col1_temp = col1;
alter table drop column col1;
alter table test rename col1_temp to col1;
alter table test add col1_temp number(5);
update test set col1_temp = to_number(col1); --增加to_number的函数
alter table drop column col1;
alter table test rename col1_temp to col1;--number(4)改成number(1):
--确认该列的数据只有一位数
alter table test modify column col2 number(1)
ORA-01438: value larger than specified precision allowed for this column
我打算先转成number(15),再转成number(5)
SQL> alter table test modify col2 number(1);
alter table test modify col2 number(1)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale