ORA-31693, ORA-02354 and ORA-01555 with Export Datapump (Doc ID 1580798.1)

本文介绍了解决Oracle数据库中使用Data Pump导出特定LOB表数据时遇到的错误问题,包括ORA-31693、ORA-02354及ORA-01555等错误的原因分析与解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天开发邮件回来说备份报错:

ORA-31693: Table data object "YCDATA"."T_MESSAGESYNC_HIS" 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 171 with name "_SYSSMU171_1375589658$" too small
调整了undo_retantion的值为3600,原来的是900
 

In this Document

Symptoms
 Cause
 Solution
 References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

Using Database Data Pump (expdp), one table fails to export with the following errors:

ORA-31693: Table data object "APPLSYS"."FND_LOBS" 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

 Followed the steps in Note:452341.1 and/or Note:787004.1, and found that there is no LOB corruption.

CAUSE

The old versions (consistent read) of the LOB can be specified by either the PCTVERSION or the RETENTION parameters.

For SecureFiles, only the RETENTION parameter can be specified.

for BasicFiles LOBs you can specify either PCTVERSION or RETENTION , but not both. 


- PCTVERSION : This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. Under 11g compatibility, this parameter is silently ignored when SecureFiles LOBs are created.

PCTVERSION is the default in manual undo mode and the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. 

- RETENTION is the default in automatic undo mode. 

You can specify the RETENTION parameter only if the database is running in automatic undo mode. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. In automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.

You can specify the optional settings after RETENTION only if you are using SecureFiles. 

You can see more details about the RETENTION parameter for SecureFiles and BasicFiles LOBs in the following link :

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45282
RETENTION Parameter for SecureFiles LOBs

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45281
RETENTION Parameter for BasicFiles LOBs


To Determine whether LOB segment is using RETENTION or PCTVERSION , use the following statement :

SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;

COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
FILE_DATA                NO                    900

 

Note : If You've a value for both PCTVERSION and RETENTION columns , this is incorrect output and to really know which option the LOB is using Please Check Note 422826.1 : How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary

 

The LOB Retention is not defined properly , This is confirmed by queries:


SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
16331

 
We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION, but the maxquery length is 16331 seconds.


When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

SOLUTION

 1. Modify the current UNDO_RETENTION for the database:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';

 

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1

SQL> alter table APPLSYS.FND_LOBS modify lob(FILE_DATA) (pctversion 5);
Table altered.

SQL> alter table APPLSYS.FND_LOBS modify lob(FILE_DATA) (retention);
Table altered.

 

3. Query the lob retention again to verify that the change has taken hold:

SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;

COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
FILE_DATA                NO                  16500

 
4. Perform the export again.

### 关于 ORA-31693 错误的解决方案 当遇到 `ORA-31693` 错误时,通常意味着在使用 Oracle 数据泵 (Data Pump) 进行导入操作 (`impdp`) 期间发生了问题。具体来说,该错误表明表数据对象未能成功加载或卸载,并因此被跳过。 #### 可能原因分析 此错误可能由多种因素引起: - **文件路径不正确**:如果指定的数据文件路径不存在或者不可访问,则可能导致此类错误发生。 - **权限不足**:执行导入命令的用户缺乏必要的目录读写权限也可能引发这个问题。 - **磁盘空间不足**:目标位置没有足够的可用存储来保存新导入的数据也会造成同样的后果。 - **其他并发错误**:有时与其他特定条件相结合会触发更复杂的场景,比如同时存在多个错误码如 `ORA-31640`, `ORA-19505`, 和 `ORA-27037` 的情况[^1]。 #### 解决方法建议 针对上述提到的各种可能性,可以采取以下措施尝试解决问题: - **验证并修正文件路径**:确认所提供的 dump 文件绝对路径无误,并且可以从当前环境中正常访问这些文件。 - **检查并调整权限设置**:确保用于运行 impdp 命令的身份具有适当的操作系统级别以及数据库内部对于涉及的对象和目录所需的全部授权。 - **清理释放更多磁盘资源**:检查目的端是否有充足的剩余容量可用于接收新的数据集;必要时删除不必要的旧备份或其他占用大量空间的内容以腾出地方。 - **处理复合型故障组合**:如果是由于多条不同类型的警告共同作用所致,则需逐一排查各个关联项下的根本成因,例如通过解决底层 I/O 访问失败(`ORA-27037`) 或者找不到物理文件 (`ORA-19505`) 来间接消除高层面上的表现形式即 `ORA-31693`. 另外值得注意的是,在某些情况下即使表已经存在于目标模式下也有可能报告类似的提示信息,这时应该考虑先移除现有同名结构再重新做一次完整的迁移过程[^5]。 ```sql -- 查询是否存在相同名称的目标表 SELECT table_name FROM all_tables WHERE owner='YOUR_SCHEMA' AND table_name='TARGET_TABLE'; -- 如果确实有冲突则可以选择重命名原表格或是直接将其drop掉(注意做好充分准备) ALTER TABLE YOUR_SCHEMA.TARGET_TABLE RENAME TO TARGET_TABLE_OLD; DROP TABLE YOUR_SCHEMA.TARGET_TABLE CASCADE CONSTRAINTS PURGE; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值