expdp时遇到ORA-01555 ORA-22924

工程师反映某医院数据库的每日expdp备份的文件越来越小，让我查一下原因。

数据库版本：10.2.0.1

打开近两天expdp执行日志发现，最大的一个表这两天都导出失败了

ORA-31693: Table data object xxx 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

看到ora-01555第一反应是undo问题，看了下undo数据文件，1个数据文件目前15G左右(开了自增),空间应该够的
undo_retention是900，直接改成了10800 

网上查了下 说带lob字段的表expdp时 ，如果lob字段存在问题的话，可能会出现这个问题
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (文档 ID 833635.1)

然后按照给出的方法，筛选出有问题的lob字段：

create table corrupted_lob_data_ (corrupted_rowid rowid); 
 declare  
  error_1555 exception;  
  pragma exception_init(error_1555,-1555);  
  num number;  
begin  
  for cursor_lob in (select rowid as r,emr_doc from emr_file ) loop  
    begin  
      num := dbms_lob.instr (cursor_lob.EMR_DOC, hextoraw ('889911')) ;  
    exception  
      when error_1555 then  
        insert into corrupted_lob_data values (cursor_lob.r);  
        commit;  
    end;  
  end loop;  
end; 

执行了好久，的确临时表里插入的32条，但我看记录的话，都是在我执行期间进行操作的记录，我试着把这记录再循环检查一次，结果第二次一条都没剩下！证明lob字段都没问题

又从网上查了查，说lob字段的undo不是存放在undo tablespace里，而是存放在自己的segment里

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. 
For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. 
For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

Note: Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undotablespace. 
For LOBs, the database attempts to honor the minimum undo retentionperiod specified by UNDO_RETENTION. 
However, if space becomes low, unexpired LOB undo information may be overwritten.

又查了查 lob字段的retention策略 有两个参数都可以决定

-----Retention和Pctversion参数的值

select TABLE_NAME, SEGMENT_NAME, PCTVERSION, RETENTION
  from dba_lobs
 where owner = 'TPHY'
   and table_name = 'EMR_FILE';

-----确认lob字段使用策略 Retention or Pctversion

select decode(bitand(flags, 32), 32, 'Retention', 'Pctversion') ||
       ' policy used'
  from lob$
 where lobj# in
       (select object_id
          from dba_objects
         where object_name in (select segment_name
                                 from dba_lobs
                                where table_name in ('EMR_FILE')
                                  and Owner = 'TPHY'));

-----修改参数值的方法

alter table EMR_FILE modify lob(EMR_DOC)(retention);---更新retention值与undo-retention一致
alter table table_name modify lob(column) (pctversion 10);

从上面查出来  我这个表的lob的retention是由Pctversion决定的 默认是10

如何辨别ORA-01555是不是发生在LOB上的:一般来说，普通的01555错误会指明发生ORA-01555的rollback segment,而LOB的则没有，而是伴随着ORA-22924出现