阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

EXPDP导出带LOB字段表报ORA-01555错误解决

235次阅读
没有评论

共计 2444 个字符,预计需要花费 7 分钟才能阅读完成。

EXPDP 导出带 LOB 字段表报 ORA-01555 错误解决

同事导出数据,结果遇到如下报错:

expdp user1/XXXXXXXX directory=szdata1 dumpfile=szhzinfo_20180319.dmp logfile=szhzinfo_20180319.log exclude=STATISTICS,INDEX tables=TKINFO QUERY=\”WHERE  voteproccesstime between 20180304000000 and 20180304235959 \”

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object “USER1″.”TKINFO” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old

遇到 ORA-01555 报错最直接的反应就是 undo 表空间大小是否足够,undo_retention 参数是否设置太小

经过验证,均不是以上问题造成的.

由于该表格有 BLOB 类型的列,经过搜索 MOS 怀疑是 BLOB 有损坏

IF: ORA-1555 Error During Export on LOB Data (文档 ID 1950937.1)
LOBs and ORA-01555 troubleshooting (文档 ID 846079.1)

开始排查是否有 LOB 字段的行存在损坏:

1. 创建表存放 lob 损坏行的 rowid

SQL> create table corrupted_lob_data (corrupt_rowid rowid, err_num number);

SQL> DESC LOBDATA

Name Null? Type
———- ——— ————
 ID NOT NULL NUMBER
 DOCUMENT BLOB

2. 执行如下 plsql 块,找出存在损坏 lob 的行

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
  for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
    begin
      n := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;
exception
when error_1578 then
insert into corrupted_lob_data values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/

Enter value for lob_column: BYTE_IMAGE
Enter value for table_owner: USER1
Enter value for table_with_lob: TKINFO
old 10: for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
new 10: for cursor_lob in (select rowid r, BYTE_IMAGE from USER1.TKINFO) loop
old 12: num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;
new 12: num := dbms_lob.instr (cursor_lob.BYTE_IMAGE, hextoraw (‘889911’)) ;

3. 查询结果发现 rowid 为 AAAhS4AAUAAE3IRAAC 的行 blob 列有损坏

SQL> select * from corrupt_lobs;

CORRUPT_ROWID ERR_NUM
—————— ———-
AAAhS4AAUAAE3IRAAC 1555

修改导出语句,跳过 blob 损坏的行,重新导出,成功导出

expdp user1/XXXXXXXX directory=szdata1 dumpfile=szhzinfo_20180319.dmp logfile=szhzinfo_20180319.log exclude=STATISTICS,INDEX tables=TKINFO QUERY=\”WHERE rowid NOT IN \(\’AAAhS4AAUAAE3IRAAC\’\) and voteproccesstime between 20180304000000 and 20180304235959 \”

更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2444字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中