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

December 17, 2023
测试
测试
测试
测试
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 \"

继续阅读

更多来自我们博客的帖子

如何安装 BuddyPress
由 测试 December 17, 2023
经过差不多一年的开发,BuddyPress 这个基于 WordPress Mu 的 SNS 插件正式版终于发布了。BuddyPress...
阅读更多
Filter如何工作
由 测试 December 17, 2023
在 web.xml...
阅读更多
如何理解CGAffineTransform
由 测试 December 17, 2023
CGAffineTransform A structure for holding an affine transformation matrix. ...
阅读更多