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”)T免费云主机域名ue 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.9999847412109SQL> 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 vWhere 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

解决方案：调大
RETENTIONSQL> 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”，共同学习，共同成长！！！

相关推荐: oracle拆分逗号分隔字符串 实现split

如果一个字符串中有像逗号或其它符号分隔，你想把它折分成列，如’first field, second field , third field’, 拆成 first field second field third field 第一种 用10G开始支持的正则表达…