Oracle field type modification solution introduction

The following articles mainly describe the actual operation scheme for modifying the field type in Oracle. Due to business needs, we need to modify the Data Type of a field, mainly to set the number (5) the actual operation that changes to number. Use the following statement if no data exists.

 
 

  
  
alter table tb_test modify permile number(5,2); 
 
 

However, if there is data, you cannot use the above method,

 
 

  
  
alter table tb_test add permile_temp number(5,2)  
  
  
update tb_test set permilepermile_temp=permile;  
  
  
alter table drop column permile;  
  
  
alter table test rename column permile_temp to permile; 
 
 

This method will change the column name, and the addition of field sequence may cause row migration, which will affect the application.

The following methods are recommended:

Table migration does not occur if the column name does not change, but the disadvantage is that the table needs to be updated twice.

If the data volume is large, more undo and redo will be generated, on the premise that the downtime is required.

If the service is not stopped, you can use the online redefinition method.

The following is a script:

 
 

  
  
alter table tb_test add permile_temp number;  
  
  
Add/modify columns  
  
  
alter table tb_test modify PERMILE null;  
  
  
update tb_test set permilepermile_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 ; 
 
 

The above content describes how to modify the field type in Oracle. I hope it will help you in this regard.