expdp ORA-01555

2022 年 02 月 21 日

375 次浏览

3741字数

环境信息：
DB:Oracle 11.2.0.1.0
OS:Windows Server 2012
---敏感数据已替换

问题：
expdp导出一张含有BLOG字段的大表(20G)时，报错ORA-01555
---expdp_log
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing 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.log
Tue Oct 30 12:05:20 2018
Archived 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 undo
NAME TYPE VALUE

undo_management string AUTO
undo_retention integer 900
undo_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还是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 ('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 undo
NAME TYPE VALUE

undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1

lob字段使用的retention还是900
SQL> 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

作者：chenoracle

expdp ORA-01555