-
Due to business needs, I need to modify the data type of a certain field from number(5) to number(5,2).
If there is no data, just use the following statement
alter table tb_test modify permile number(5,2);
But if you have data, you cannot use the above method.
alter table tb_test add permile_temp number(5,2)
update tb_test set permile_temp=permile;
alter table drop column permile;
alter table test rename column permile_temp to permile;
This method will cause the column names to change, and increasing the field order may cause row migration, which will have an impact on the application.
The following method is a better method
There is no need to change the column names and no table migration will occur, but this has the disadvantage that the table needs to be updated twice.
If the amount of data is large, more undo and redo will be generated, and the prerequisite is to shut down the machine.
If there is no downtime, you can also use online redefinition.
Here is the script:
alter table tb_test add permile_temp number;
Add/modify columns
alter table tb_test modify PERMILE null;
update tb_test set permile_temp=permile,permile=null;
commit;
alter table tb_test modify permile number(5,2);
update tb_test set permile=permile_temp,permile_temp=null;
commit;
alter table tb_test drop column permile_temp;
alter table tb_test modify PERMILE not null;
select * from tb_test;
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/luinstein/archive/2009/12/21/5049010.aspx