一般情况下当我们遇到ORA-01555: snapshot too old的时候，我们第一反应就是undo不够，但是对于lob类型来说除了有可能是old version不够外，我们还要注意有可能是LOB segment corruption。我就遇到过这么一个情况，在一个没有操作的库上expdp一个含NCLOB的表时报ORA-01555: snapshot too old，让人很纳闷，问题就出在LOB segment corruption。有两点建议：
一是LOB字段最好别用，oracle对lob字段的支持不好，导出来也奇慢无比。
二是如果你在查询含LOB字段的表时出现ORA-01555: snapshot too old，需要有多种视角。

一导出该表时遇到ORA-01555: snapshot too old：

[oracle@db-15 pump]$ expdp  \'\/ as sysdba \'  tables=test_sch.nclob_tab directory=pump  DUMPFILE=nclob_tab.dmp

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 16 March, 2012 10:28:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=test_sch.nclob_tab directory=pump DUMPFILE=nclob_tab.dmp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 15.21 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "TEST_SCH"."NCLOB_TAB" 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 "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /data/oracle/pump/nclob_tab.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 10:31:53

二因为当时数据库没人操作，是clean shutdown后startup的，排除old version不够的原因，怀疑有LOB segment corruption。通过如下方式来检测该字段：

create table corrupted_data (corrupted_rowid rowid);
 
set concat off
declare
  error_1555 exception;
  pragma exception_init(error_1555,-1555);
  v_lob NCLOB;
  n number;
begin
   for cursor_lob in (select rowid r  from test_sch.NCLOB_TAB) loop
   begin
     select  PT_DESC into v_lob  from   test_sch.NCLOB_TAB  where rowid=cursor_lob.r;
     n:=dbms_lob.instr(v_lob,hextoraw('889911')) ;
   exception
     when error_1555 then
       insert into corrupted_data values (cursor_lob.r);
       commit;
   end;
  end loop;
end;
/
undefine lob_column
 
 
之后查看 corrupted_data 发现17条记录存在问题： 
 
sys@item> select * from corrupted_data;
 
CORRUPTED_ROWID
------------------
AAAEKcAAMAABhYrAAA

AAAEKcAAMAABhZrAAA
AAAEKcAAMAABhXSAAG
 
17 rows selected.
 
 
查看任意一条记录 报错： 
select  PT_DESC  from  test_sch.NCLOB_TAB where rowid='AAAEKcAAMAABhYrAAA'; 
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
 
 
这17条记录的NCLOB 都出现了问题，下面修复这些错误的column 信息  
 
update test_sch.NCLOB_TAB set  PT_DESC=null where rowid in (select CORRUPTED_ROWID from  corrupted_data);

三 可以参考这个文章：

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2   [Release: 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Following errors encountered during export:

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old

Cause

LOB segment corruption.