Steps for modifying the field type in Oracle

The following articles mainly describe the actual operation scheme for modifying the field type in Oracle. We all know that the actual application of modifying the field type in Oracle is more common, it is advantageous to understand the actual operation steps. The following describes the specific content of the article.

To modify the Data Type of a field as required by the business, change it to number (5 ).

C. Use the following statement if there is no data

 
 

  
  
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; 
 
 

Modifying the field type in Oracle may change the column name, and row migration may occur when the field order is increased, which may 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 is an introduction to the method for modifying the field type in Oracle. I hope you will find some gains.