oracle column to,Oracle SQL to change column type from number to varchar2 while it contains data

问题

I have a table (that contains data) in Oracle 11g and I need to use Oracle SQLPlus to do the following:

Target: change the type of column TEST1 in table UDA1 from number to varchar2.

Proposed method:

backup table

set column to null

change data type

restore values

The following didn't work.

create table temp_uda1 AS (select * from UDA1);

update UDA1 set TEST1 = null;

commit;

alter table UDA1 modify TEST1 varchar2(3);

insert into UDA1(TEST1)

select cast(TEST1 as varchar2(3)) from temp_uda1;

commit;

There is something to do with indexes (to preserve the order), right?

回答1:

create table temp_uda1 (test1 integer);

insert into temp_uda1 values (1);

alter table temp_uda1 add (test1_new varchar2(3));

update temp_uda1

set test1_new = to_char(test1);

alter table temp_uda1 drop column test1 cascade constraints;

alter table temp_uda1 rename column test1_new to test1;

If there was an index on the column you need to re-create it.

Note that the update will fail if you have numbers in the old column that are greater than 999. If you do, you need to adjust the maximum value for the varchar column

回答2:

Add new column as varchar2, copy data to this column, delete old column, rename new column as actual column name:

ALTER TABLE UDA1

ADD (TEST1_temp VARCHAR2(16));

update UDA1 set TEST1_temp = TEST1;

ALTER TABLE UDA1 DROP COLUMN TEST1;

ALTER TABLE UDA1

RENAME COLUMN TEST1_temp TO TEST1;

回答3:

Look at Oracle's package DBMS_REDEFINE. With some luck you can do it online without downtime - if needed. Otherwise you can:

Add new VARCHAR2 column

Use update to copy NUMBER into VARCHAR2

Drop NUMBER column

Rename VARCHAR2 column

来源：https://stackoverflow.com/questions/18978246/oracle-sql-to-change-column-type-from-number-to-varchar2-while-it-contains-data