How Oracle modifies the data type when the column is not empty

– New temporary columns

ALTER TABLE TableName Add filedname_temp number (2);

– Empty the value of the temporary column

Update Zyt set id_temp=null; -----#alter Table TableName modify filedname null;

– Move the value of the field you want to update to a temporary column and empty the column

Update tablename set filedname_temp=filedname,filedname=null;

Commit

– Modify the column's data type to VARCHAR2

ALTER TABLE TableName Modify Filedname varchar2 (20);

– Move the temporary column value back to the column and empty the temporary column

Update tablename set filedname=filedname_temp,filedname_temp=null;

Commit

– Delete temporary columns

ALTER TABLE tablename drop column filedname_temp;

– cannot be empty for this column

ALTER TABLE TableName modify filedname NOT null;

– Perform query tests

SELECT * FROM TableName;

In this way, you don't have to change the name of the column. Table migration will not occur, but there is a disadvantage is that the table to update two times, and when the amount of data, resulting in the undo and redo also more, the premise is to stop the operation, if not down, you can also use the online redefinition way to do.

Note: Please change the TableName and filedname for your own actual value.