expdp导库的时候，日志提示：
ORA-31693: Table data object "YN01700_FRAME"."TAPAGEREVIEW" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
遇到ORA-01555报错最直接的反应就是undo表空间大小是否足够，undo_retention参数是否设置太小，
如果是这种情况，解决方法为：
1.alter table table_name modify lob(column) (pctversion 80);
2.alter system set undo_retention=180000;
经过验证，均不是以上问题造成的.
由于该表有BLOB和CLOB类型的列，怀疑是BLOB有损坏，解决方法如下：
1.创建表存放lob损坏行的rowid
create table corrupted_lob_data_cbill_cloud (corrupt_rowid rowid, err_num number);
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);
begin
for cursor_lob in (select rowid r, CONTEXT from YN01700_FRAME.TAPAGEREVIEW) loop
begin
n := dbms_lob.instr(cursor_lob.CONTEXT, hextoraw('889911'));
exception
when error_1578 then
insert into corrupted_lob_data_cbill_cloud
values
(cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data_cbill_cloud
values
(cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data_cbill_cloud
values
(cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
3.查询结果查看哪些行 blob列有损坏
select * from corrupted_lob_data_cbill_cloud;
4.将损坏的blob列置空
update table_name set column = EMPTY_BLOB() where rowid in (select corrupt_rowid from corrupted_lob_data_cbill_cloud);
select * from YN01700_FRAME.TAPAGEREVIEW where PAGE_ID='46132332' and rowid='AAAbXYACVAACK7tAAC';
select * from YN01700_FRAME.TAPAGEREVIEW where PAGE_ID='46132313' and rowid='AAAbXYACVAACLBTAAD';
update YN01700_FRAME.TAPAGEREVIEW set CONTEXT=EMPTY_BLOB(),DATA=EMPTY_BLOB() where PAGE_ID='46132332' and rowid='AAAbXYACVAACK7tAAC';
update YN01700_FRAME.TAPAGEREVIEW set CONTEXT=EMPTY_BLOB() where PAGE_ID='46132313' and rowid='AAAbXYACVAACLBTAAD';
-- 至此问题解决
ALTER TABLE YN01700_FRAME.TAPAGEREVIEW MODIFY LOB(CONTEXT)(pctversion 80);
ALTER TABLE YN01700_FRAME.TAPAGEREVIEW MODIFY LOB(DATA)(pctversion 80);
ALTER TABLE YN01700_FRAME.TAPAGEREVIEW MODIFY LOB(RESULT_BEAN)(pctversion 80);
导出命令记录：
expdp system/oracle11 directory=OLD tables=YN01700_FRAME.TAPAGEREVIEW query=YN01700_FRAME.TAPAGEREVIEW:\"where rowid in \( select corrupt_rowid from sys.corrupted_lob_data_cbill_cloud\)\" dumpfile=old414_%U.dmp logfile=old414.log parallel=40
YN01700_FRAME.TAPAGEREVIEW:\"where rowid in \( select corrupt_rowid from sys.corrupted_lob_data_cbill_cloud\)\"
-- mos ways of resolution
Description
ORA-600 [7999] [9] [1] [<lob block rdba>] / ORA-1555 caused by
a double allocated LOB block after a LOB column UPDATE.
The second argument [1] in the ORA-600 [7999] indicates ORA-1.
The problem is introduced by an UPDATE producing an ORA-1551 (internal
error not visible to user) of a LOB column based on a subquery. The
statement is basically of the form:
update <TABLE1> set <COLUMN1> = (select to_lob(<COLUMN1>) from <TABLE2> where <TABLE1>.<PK COLUMN> = <TABLE2>.<PK COLUMN>)
Where <TABLE1>.<COLUMN1> is a CLOB column and <TABLE2>.<COLUMN1> is a LONG column.
This problem may occur in both ASSM and MSSM tablespaces.
If the problem is detected during SMON transaction recovery, it may cause a
hang / spin which is fixed by Bug 11790175 .
The affected rows can be identified by running the next plsql:
find_lob.sql :
set echo on
TRUNCATE TABLE CORRUPT_LOBS;
CREATE TABLE CORRUPT_LOBS (corrupt_rowid ROWID, error_number NUMBER);
set echo off
set feedback off
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
set serveroutput on
set head off
set timing on
set verify off
set concat off
declare
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
invalid_rows number := 0;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_name) loop
begin
n := dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
exception
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r,1555);
commit;
invalid_rows := invalid_rows+1;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r,22922);
commit;
invalid_rows := invalid_rows+1;
end;
end loop;
if invalid_rows > 0 then /* plsql found invalid rowids */
dbms_output.put_line('==============================================');
dbms_output.put_line('Problem found in '||invalid_rows||' rows:');
dbms_output.put_line('==============================================');
else
dbms_output.put_line('No problems identified');
end if;
end;
/
set timing off
undefine lob_column
select CORRUPT_ROWID
, dbms_rowid.rowid_relative_fno(CORRUPT_ROWID) rfile#
, dbms_rowid.rowid_block_number(CORRUPT_ROWID) block#
, dbms_rowid.ROWID_ROW_NUMBER(CORRUPT_ROWID) row#
, error_number
from corrupt_lobs;
-- 完整的处理方式及导出记录
-- 记录一次LOB损坏导致的EXPDP导出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);
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 corrupted_lob_data;
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 \"
