EXPDP for a large table fails with ORA-1555

수정 날짜 20-MAY-2010     유형 PROBLEM     상태 PUBLISHED

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

Running an expdp on a certain table usually takes an hour.
However, it now runs for over a day. The problem appears to be with one table (the largest in the database).
When the expdp is started the first table that it processes has an estimated process time of over 24hrs.

When the same export is run from two other servers, it shows the elapsed time for the same table as around 20 minutes


ERROR
-----------------------
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
ORA-31693 : Table data object "MERIDIAN"."LINEPAST" 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 1 with name "_SYSSMU1$" too small

The table does not have any LOB columns.
Increasing the undo_retention parameter did not help.

Changes

Most of the rows in the table were recently updated.

Cause

Updating the rows in the table have caused high fragmentation (chained/migrated rows), which increased significantly the time to perform the export of this table.

Solution

1.Recreate the fragmented table through : 
SQL> create table <new_table_name> as (select * from <fragmented_table);

then drop the fragmented table.

You may re-name the <new_table_name> to the old table name.

2. Perform the expdp job. (this time it ended in few minutes) 

You may also refer to Note 746778.1 How to Identify, Avoid and Eliminate Chained and Migrated Rows ?

References

NOTE:746778.1 - How to Identify, Avoid and Eliminate Chained and Migrated Rows ?

관련 자료 제품 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 오류 ORA-31693; ORA-2354; ORA-1555 출처 : oracle support EXPDP_ORA-1555 Err 해결방법.docx                                                          ('' ) NOTE:746778.1의 내용