FDATAVALUE from T_WFR_PROCINSTDATA
T_WFR_PROCINSTDATA的LOB字段FDATAVALUE损坏或导出、查询失败
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_WFR_PROCINSTDATA MODIFY LOB(FDATAVALUE)(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, FDATAVALUE from T_WFR_PROCINSTDATA) loop
begin
n:=dbms_lob.instr(cursor_lob.FDATAVALUE,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_WFR_PROCINSTDATA set FDATAVALUE = empty_Clob() where rowid in (select corrupt_rowid from corrupt_lobs);
commit;
FDATAVALUE from T_WFR_PROCINSTDATA
本文2024-09-22 20:22:37发表“eas cloud知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-eas-113233.html