expdp ORA-01555(一)

expdp ORA-01555(一) 环境信息： DB:Oracle 11.2.0.1.0 OS:Windows Server 2012---敏感数据已替换 问题： expdp导出一张含有BLOG字段的大表(20G)时，报错ORA-01555---expdp_logProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-31693: Table data object "CHENJCH"."T_XXX_XXXXX" 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 10 with name "_SYSSMU10_XXXXXXXXX$" too small---alert_XXX.logTue Oct 30 12:05:20 2018Archived Log entry 6560 added for thread 1 sequence 46359 ID 0x7262bbad dest 1:Tue Oct 30 13:19:12 2018 ORA-01555 caused by SQL statement below (SQL ID: 88tquba1dj6s0, SCN: 0x0000.47dd74a2):SELECT * FROM RELATIONAL("CHENJCH"."T_XXX_XXXXX")Tue Oct 30 13:29:42 2018 问题分析： ORA-01555问题一般有两个原因： (1)UNDO表空间不足 (2)undo_retention时间太小---查看UNDO表空间还有很大剩余 SQL> select bytes / 1024 / 1024 / 1024,             tablespace_name,             autoextensible,             maxbytes / 1024 / 1024 / 1024        from dba_data_files a       where tablespace_name = 'UNDOTBS1';BYTES/1024/1024/1024 TABLESPACE_NAME                AUTOEXTENSIBLE MAXBYTES/1024/1024/1024-------------------- ------------------------------ -------------- -----------------------        1.4306640625 UNDOTBS1                       YES                   31.9999847412109 SQL> select segment_name,       tablespace_name,       r.status,       (initial_extent / 1024) InitialExtent,       (next_extent / 1024) NextExtent,       max_extents,       v.curext CurExtent  From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+)   and segment_name = '_SYSSMU10_XXXXXXXXX$'; SEGMENT_NAME                   TABLESPACE_NAME                STATUS           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT ------------------------------ ------------------------------ ---------------- ------------- ---------- ----------- ---------- _SYSSMU10_XXXXXXXXX$           UNDOTBS1                       ONLINE                     128         64       32765          3 ---查看undo_retention为默认值900秒SQL> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS1  ---查看retention为默认值900秒，PCTVERSION为空SQL> select column_name, pctversion, retention       from dba_lobs      where table_name = 'T_XXX_XXXXX'        and owner = 'CHENJCH';COLUMN_NAME      PCTVERSION  RETENTION---------------- ---------- ----------FFILE                           900 ---查看当前使用retention还是PCTVERSIONselect 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 ('T_XXX_XXXXX')                                  and owner = 'CHENJCH'));DECODE(BITAND(FLAGS,32),32,'RE------------------------------Retention policy used      SQL> select max(maxquerylen) from v$undostat;MAX(MAXQUERYLEN)----------------            1939   解决方案：调大 RETENTION SQL> ALTER SYSTEM SET UNDO_RETENTION = 3600 scope=both;   SQL> SHow parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     3600undo_tablespace                      string      UNDOTBS1 ---lob字段使用的retention还是900SQL> select column_name, pctversion, retention       from dba_lobs      where table_name = 'T_XXX_XXXXX'        and owner = 'CHENJCH';COLUMN_NAME      PCTVERSION  RETENTION---------------- ---------- ----------FFILE                           900 ---lob字段使用的retention需要在执行一次SQL> alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (retention);  ---ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效---等一会在执行Table altered---alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (pctversion 5); SQL> select column_name, pctversion, retention       from dba_lobs      where table_name = 'T_XXX_XXXXX'        and owner = 'CHENJCH';   COLUMN_NAME      PCTVERSION  RETENTION---------------- ---------- ----------FFILE                           3600 ---回退操作---ALTER SYSTEM SET UNDO_RETENTION = 900 scope=both;---alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (retention); 参考： Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No LOB Corruption (文档 ID 1507116.1) https://support.oracle.com/epmos/faces/SearchDocDisplay?_afrLoop=337135896307291&_afrWindowMode=0&_adf.ctrl-state=1bqt29sg65_4 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！