Oracle methods for modifying field types

Today company because of business needs, repair to modify a field data type has number (5), changed to number (5,2) type

If you don't have the data, just use the following statement

ALTER TABLE Tb_test modify Permile number (5,2);

But with the data, you can't use the method above,

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 approach causes the column names to change, and the field order increases with the potential for row migrations, which can have an impact on the application

The following methods are a better approach

You do not have to change the column name and the table migration does not occur, but there is a disadvantage that the table is updated two times

If the amount of data is large, the resulting undo and redo more, the premise is to stop doing

If you do not stop, you can also use the online redefinition method to do

Here's 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;

Oracle methods for modifying field types (GO)