Oracle Field Type Change

Oracle changed Field Type 1. If the field size is changed simply, for example, numeric () --- & gt; numeric ), you can directly alter table name modify (field name numeric () 2). Due to requirements changes, you need to change the field type of a NUMBER () to char. The general idea of www.2cto.com is as follows: Rename the field name of the type to be changed to backup, add a field with the same name as the field name of the type to be changed (the original field has been renamed), and then update the data, finally, delete the renamed backup field. The following operations are performed in Oracle 10.2.0.1.0. /* Modify the original field name */alter table name rename column field name TO field name 1; /* ADD a field with the same name as the original field */alter table name ADD field name VARCHAR2 (30);/* update the original data to the new field, be sure to display the data type conversion (different from MSSQL) */UPDATE table name SET field name = CAST (field name 1 AS VARCHAR2 (30 )); /* Delete the original backup field */alter table Name drop column field name 1; other solutions: today, due to business needs, the company needs to modify the Data Type of a field with number (5 ), if no data exists, use the following statement to alter table tb_test modify permile number (). However, if data exists, you cannot use the preceding 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 may change the column name, and the addition of the field sequence may cause row migration, which may affect the application. The following method is a good method to avoid column name changes or table migration, however, this disadvantage is that the table needs to be updated twice. If the data volume is large, more undo and redo are generated. The premise is that the table needs to be stopped, you can also use the online redefinition method to execute the following 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;