Oracle 列由VARCHAR2改为CLOB类型

最近突然遇到好多Oracle 列由VARCHAR2改为CLOB类型的需求，不知道大家是不是约好了。。。查了一些网上的文章，结合实际修改的情况记录一下各种场景及解决方法。

 

一、 示例准备

新建两张表TB_WITHOUT_DATA（VARCHAR2列不包含数据）和TB_WITH_DATA（Varchar2列包含数据)

create table TB_WITHOUT_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

create table TB_WITH_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);
 
insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');
insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');
insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');
insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');
insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');
insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');
commit;

错误方法

ALTER TABLE TB_WITHOUT_DATA MODIFY description clob;

错误信息

SQL Error: ORA-22858: invalid alteration of datatype
22858. 00000 -  "invalid alteration of datatype"
*Cause:    An attempt was made to modify the column type to object, REF,
           nested table, VARRAY or LOB type.
*Action:   Create a new column of the desired type and copy the current
           column data to the new type using the appropriate type
           constructor.

 

二、 解决方法

1. 待修改列无数据

• 列上无约束

首先把该列改成Long类型，然后再改成clob类型

alter table TB_WITHOUT_DATA modify description long;--首先改成Long类型
alter table TB_WITHOUT_DATA modify description clob;--在Long类型的基础上改成clob类型

对于此列已经存在数据的，不能通过此方法，否则会报如下错误：

alter table TB_WITH_DATA modify description long;--更改包含数据的列
SQL Error: ORA-01439: column to be modified must be empty to change datatype
01439. 00000 -  "column to be modified must be empty to change datatype"

• 列上有约束

如果列上有约束改为long类型也会报错

解决方法：

1）删除约束后再改，改完再建约束。

2）既然都是空的，drop掉这列直接加个clob类型列就好了，记得把约束建回去（建议先建测试表试下导入数据是否有问题）

 

2. 待修改列包含数据

注意此方法需要停业务，不然新数据会丢。当然不包含数据也可以用这种，但没必要。

1）重命名原列

alter table TB_WITHOUT_DATA rename column description to description_bak;
alter table TB_WITH_DATA rename column description to description_bak;

2）新建clob列

alter table TB_WITHOUT_DATA add description clob;
alter table TB_WITH_DATA add description clob;

3）将原列中数据更新到clob列

update TB_WITH_DATA set description=description_bak;
commit;

4）删除原列

alter table TB_WITHOUT_DATA drop column description_bak;
alter table TB_WITH_DATA drop column description_bak;

5）验证

表结构验证

DESC TB_WITHOUT_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB 

DESC TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB  

数据验证

select * from TB_WITH_DATA;
 
        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie                       

 

3. 待修改列包含数据，另一种方法

注意此方法需要停业务，不然新数据会丢。当然不包含数据也可以用这种，但没必要。

1）重命名业务表

alter table TB_WITH_DATA rename to TB_WITH_DATA_BAK;

2）通过以下语句创建新表

create table TB_WITH_DATA as select id, name, to_clob(description) description from TB_WITH_DATA_BAK;

表结构与数据验证

desc TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB 

select * from TB_WITH_DATA;
 
        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie      

3）删除备份表

DROP TABLE TB_WITH_DATA_BAK;

 

参考

https://blog.csdn.net/jssg_tzw/article/details/40829867