T_BAS_Attachment的LOB字段FFILE损坏或导出、查询失败

栏目:eas cloud知识作者:金蝶来源:金蝶云社区发布:2024-09-22浏览:1

T_BAS_Attachment的LOB字段FFILE损坏或导出、查询失败

T_BAS_Attachment的LOB字段FFILE损坏或导出、查询失败

 ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small

ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

""里有内容的,比如"_SYSSMU10$" ,也可能""里没有内容,可以先查看是否超时导致,可以设置参数、加大undo表空间解决,设置完再查询或导出:
alter system set undo_retention=172800 ;
ALTER TABLE T_BAS_Attachment MODIFY LOB(FFILE)(retention);

如果上面方法不行,可能是LOB的字段损坏,在select或update或insert或delete语句中,只要访问到有CLOB的那一行数据就会报这个错。
 
修复方法:
步骤1:
使用eas用户在sqlplus登录,并创建临时表
sqlplus eas用户名/密码
drop table corrupt_lobs; --如果之前没有建过这个表,会出错,请忽略错误

create table corrupt_lobs (corrupt_rowid rowid, err_num number);

set serveroutput on
declare
  error_1578 exception;
  error_1555 exception;
  error_22922 exception;
 error_22924 exception;
  pragma exception_init(error_1578,-1578);
  pragma exception_init(error_1555,-1555);
  pragma exception_init(error_22922,-22922);
  pragma exception_init(error_22924,-22924);
  n number;
begin
  for cursor_lob in (select rowid r, FFILE from  T_BAS_ATTACHMENT) loop
  begin
    n:=dbms_lob.instr(cursor_lob.FFILE,hextoraw('889911'));
  exception
    when error_1578 then
      insert into corrupt_lobs values (cursor_lob.r, 1578);
      commit;
    when error_1555 then
      insert into corrupt_lobs values (cursor_lob.r, 1555);
      commit;
    when error_22922 then
      insert into corrupt_lobs values (cursor_lob.r, 22922);
      commit;
    when error_22924 then
      insert into corrupt_lobs values (cursor_lob.r, 22924);
      commit;
    end;
  end loop;
end;
/
执行完上面代码后,有LOB字段损坏的行的rowid就会被记录在临时表corrupt_lobs中

下面语句是对坏的那行的LOB字段做修复,被修复行的该LOB字段内容将被清空:
  update  T_BAS_ATTACHMENT set FFILE= empty_Blob()   where rowid in (select corrupt_rowid from corrupt_lobs);
commit;

T_BAS_Attachment的LOB字段FFILE损坏或导出、查询失败

T_BAS_Attachment的LOB字段FFILE损坏或导出、查询失败 ORA-01555: snapshot too old: rollback segment number 10 with name "_S...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息