Common Methods for modifying related fields in Oracle

The following articles mainly introduce several common types of Oracle field modification and the actual operation methods. Due to business needs, you need to modify the Oracle field, the data type is number (5) and changed to number (5, 2). If there is no data, use the following statement.

 
 

  
  
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.