记一则Oracle数据库的表字段类型修改踩过的坑

故事剧情:
公司某产品收下位系统商抛出来的SOAP消息,做完业务逻辑处理将完整的XML消息报文写入数据库存储备查。之前一直正常,最近突然发现系统频频报错,大致提示内容Oracle写入数据库错误,字段长度7000+,超过4000

问题

这个问题其实蛮常见,描述也异常的清晰,直接查看了一下数据库设置,相应的字段设置为VARCHAR,Oracle最大长度定义为4000,显然是经验欠缺,对字段长度预估不到位造成。下位系统此前一直按照标准方式一次性发一台设备的信息,现在出于减少消息并发次数考虑,将能合并的消息组合到一条SOAP里面一次性丢,原则上正确,但我方这边写数据库已报错了。

排错过程

  1. 因为这张表的目的就是为了记录消息履历备查,把XML消息分段拆开存表有点本末倒置了,放弃
  2. 只剩一条路了吧,改表的字段类型吧,最合适就是将VARCHAR改去CLOB了

踩坑过程

  1. 以下我会详细描述一下排错过程,由于是数据库小白,可能犯了很多二逼的错误,麻烦各位不吝赐教,最终解决方式和事故总结可以直接跳到文末查看。
  2. 求助度娘,很容易找到如下的解,因为已被人转帖太多次,找不到原作者了,无法致谢,再次鞠个躬。
--首先给原来varchar2类型的字段MESSAGEBODY改名为Temp
alter  table   表名  rename  column  MESSAGEBODY  to  Temp;

--增加字段类型为long的字段MESSAGEBODY
alter  table  表名  add  MESSAGEBODY  long;

--给新增的MESSAGEBODY赋值为Temp.
update  表名   set  MESSAGEBODY= Temp;

--修改MESSAGEBODY的类型从long为clob。
alter  table  表名  modify(MESSAGEBODY  clob);

--删除Temp列
alter table 表名 drop (Temp)
  1. 打开本地的Oracle测试验证了一下,秒秒钟执行成功,相关字段类型已改,原表数据都还在,数据库查询写入完全正常。
  2. 放手干吧,和客户窗口打好招呼,开始动手
  3. 当我将相关的SQL丢到客户服务器的SQL Developer上,按下运行之后,一股不祥的预感就从菊花涌了上来,SQL执行到第三句的时候:
--给新增的MESSAGEBODY赋值为Temp.
update  表名   set  MESSAGEBODY= Temp;

开始转圈,然后就是漫长的等待,大约3分钟过去了,我有点方,于是打开了SQL Developer的编辑页面,发现这张表的TEMP列已经有了,MESSAGEBODY字段也改成LONG了,看了第一句和第二句SQL是完整结束了,于是手贱尝试在编辑页面直接删掉TEMP,同时改MESSAGE从LONG到CLOB

  1. 报错开始了。。。

ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

  1. 可能是因为对表的操作短时间内太过快速和频繁而导致的数据库锁表了,我事后诸葛亮推测可能有两个原因,一个是SQL Developer自身锁的,因为我的第三句给新增的MESSAGEBODY赋值为Temp的SQL还在执行,同时我又去操作了编辑表的页面动作;一个是我的程序其实没有关闭,还在持续运行,有可能有执行写入表的操作冲突了。
  2. 这里关于ORA-00054解锁的方式就不赘述了,转帖一下ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效—解决方法
  3. 需要注意的事,大概率执行完第8步的解锁动作,依然无法对表进行操作,大致提示意思是这个Session已经被老子标记为Kill了,小朋友不要急嘛,等会儿就会帮你Kill咯。。。
  4. 原因:alter system kill session实际上不是真正的杀死会话,它只是将会话标记为终止。等待PMON进程来清除会话。怎么解决咧?再次转帖:ORACLE快速彻底Kill掉的会话
  5. 如是这番反复试了几次杀Session,杀进程,终于发现表能操作了,我长吁一口气,这次跟客户打好招呼,关掉了程序,再乖乖的使用SQL Developer的编辑表页面来清理那个临时的TEMP列以及修改MESSAGE为CLOB
  6. 然而但是!点击完确定,依然是漫长的等待,10分钟都过去了还在刷进度条,我心态崩了,点了一下取消。。。
  7. 果然,第5步那个该死的ORA-00054报错又出现了,再次被锁表,凶手看来就是SQL Developer无疑了的,但是原因呢?
  8. 先解决问题,重复第8步~第10步的步骤,再次花了点时间把Session杀干净,然后这次决绝了一点,不考虑数据完整性了,先清空表,再操作(好在客户好说话,并且这个项目还在测试阶段)
  9. 这次我直接使用SQL Developer的编辑表页面操作,执行效率就很快了,30s不到修改完成,重启程序,一切恢复。
  10. 好在是我在清表之前有留意下了一下这张表当前的数据量,总算找到了真相:表内已有10W+的数据量,不多不少,但是在执行
--给新增的MESSAGEBODY赋值为Temp.
update  表名   set  MESSAGEBODY= Temp;`

这句SQL的时候应该是复制原字段内内容的缘故,耗时较长,此时无论是我不耐烦点取消的缘故或是我的程序本身写入数据库的缘故,都可能导致锁表的发生。

总结

  1. 当发生类似表内已经有大量数据仍然需要修改表字段类型的情况时,清表再修改当然最方便,但是大多数情况下可能不被允许,相应的,上面的SQL是可以安全执行的,但是由于给新增的MESSAGEBODY赋值为Temp这个操作耗时可能很长,需要权衡利害
  2. VARCHAR直接改CLOB是不行的,需要通过LONG在中间转一下,即VARCAR=>LONG=>CLOB。网上也有人说不需要可以直接转,可能是跟Oracle版本差异有关,我以上故事发生在Oracle 12c上,供参考。
  3. 贴上我操作用的SQL:
--变更XX表MESSAGEBODY字段为CLOB

--1.清空XX表内数据
truncate table 表名;

--2.首先给原来varchar2类型的字段MESSAGEBODY改名为Temp
alter  table   表名  rename  column  MESSAGEBODY  to  Temp;

--3.增加字段类型为long的字段MESSAGEBODY
alter  table  表名  add  MESSAGEBODY  long;

--4.给新增的MESSAGEBODY赋值为Temp.
update  表名   set  MESSAGEBODY= Temp;

--5.修改MESSAGEBODY的类型从long为clob。
alter  table  表名  modify(MESSAGEBODY  clob);

--6.删除Temp列
alter table 表名 drop (Temp);
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页