同事导出数据, 结果遇到如下报错:
- 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 \"
来源: http://www.linuxidc.com/Linux/2018-03/151632.htm