ORA-22858: invalid alteration of datatype(varchar2转化clob)

ORA-22858: invalid alteration of datatype


由于业务的需要,今天研发同事问我在oracle里如何把table的varchar2类型转化为clob类型,其实标量类型的变量可以直接转换。


在'列'没有数据时,可以直接修改

alter table table_name modify column_name new_databyte;


在有数据的时候,可以删除和新建列或者通过一个中间列来修改原列的类型

1.

alter table table_name add column_new number;
update table_name set column_new=column_old;
alter table table_name drop column column_old;
alter table table_name rename column column_new to column_old;


例子:

查看现有表结构:
SQL> desc test_skate;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
NAME CLOB Y
ID NUMBER Y

添加新列:
SQL> alter table test_skate add id_new varchar(50);
Table altered

备份原列值:
SQL> update test_skate set id_new=id;
1 row updated

删除原列
SQL> alter table test_skate drop column id;
Table altered

把新列重命名为原列名:
SQL> alter table test_skate rename column id_new to id;
Table altered

SQL> desc test_skate;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
NAME CLOB Y
ID VARCHAR2(50) Y


这种方法是把新增加的字段重命名为要转换的字段,字段的顺序发生了变化,如果要求字段顺讯不变,那就还要使用
原来的字段,思路是先把现在列的数据转移到新增加的列上,然后修改原列的类型,再把数据转移回来,对原列数据
要更新两次。如果数据量比较大的话,会产生大量的undo和redo;也会产生大量的阻塞;如果想对现有系统影响最小
那就采用表在线重定义的方式

alter table table_name add cloumn_new number;
alter table table_name modify cloumn_old null;
update table_name set cloumn_new=cloumn_old,cloumn_old=null;
commit;
alter table table_name modify cloumn_old number(10,2);
update table_name set cloumn_old=cloumn_new,cloumn_new=null;
commit;
alter table tb_test drop column cloumn_new;
alter table tb_test modify cloumn_old not null;
select * from tb_test ;


例子:

查看现有表结构:
SQL> desc test_skate;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
NAME CLOB Y
ID VARCHAR2(50) Y
TEXT VARCHAR2(100) Y

SQL> select * from test_skate;
NAME ID TEXT
----- --------------------
1 222
1 222
1 222
1 222

添加新列
SQL> alter table test_skate add id_new varchar(100);
Table altered

编辑原列可以为null
SQL> alter table test_skate modify id null;
alter table test_skate modify id null
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> alter table test_skate modify id not null;
Table altered

SQL> alter table test_skate modify id null;
Table altered

把原列数据复制到新列
SQL> update test_skate set id_new=id,id=null;
4 rows updated

SQL> commit;
Commit complete

更该原列的数据类型
SQL> alter table test_skate modify id number;
Table altered

把新列的数据复制到原列
SQL> update test_skate set id=id_new,id_new=null;
4 rows updated

SQL> commit;
Commit complete

删除新列
SQL> alter table test_skate drop column id_new;
Table altered

编辑原列不为null
SQL> alter table test_skate modify id not null;
Table altered

查看现有结构
SQL> desc test_skate;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
NAME CLOB Y
ID NUMBER
TEXT VARCHAR2(100) Y

SQL>



但是如果要把varchar2类型转化为log类型,就要用些特殊手段了
例如:

SQL> create table test_skate
2 (
3 name varchar2(4000)
4 )
5 tablespace TBS_ARENA
6 ;
Table created


及时表为空,也不让更改

SQL> alter table test_skate modify name clob;
alter table test_skate modify name clob
ORA-22858: invalid alteration of datatype


这里可以借助long类型过度,如果记录为空,可以直接修改为LONG类型,在从long转化为clob,对于LONG类型,不管有没有数据存在,
可以直接修改为CLOB类型

SQL> alter table test_skate modify name long;
Table altered

SQL> alter table test_skate modify name clob;
Table altered

SQL> desc test_skate;
Name Type Nullable Default Comments
---- ---- -------- ------- --------
NAME CLOB Y

对于LONG类型的转换,Oracle并不是简单的将列的定义换成CLOB,而是生成了一个临时列,将数据保存,然后删除原LONG列。
Oracle对LONG类型的转换操作进行封装,效率比用其他方法要高。


上面是在没有数据情况下, 如果有数据,和上面类似,就不测试了。



-----end-----
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值