EXPDP for a large table fails with ORA-1555
Oracle 2011. 8. 11. 11:30| 수정 날짜 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.2Information 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 support ('' ) NOTE:746778.1의 내용 | ||
EXPDP_ORA-1555 Err 해결방법.docx'Oracle' 카테고리의 다른 글
| Bug 5387030: AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS (0) | 2011.08.11 |
|---|---|
| Oracle 10gR2이상에서 Automatic Tuning of Undo_retention Causes Space Problems (1) | 2011.08.11 |
| [Oracle Dictionary] PL/SQL과 Table간의 Dependency 정보 확인 (0) | 2011.02.22 |
| Oracle REF CURSOR (0) | 2011.02.18 |
| [Oracle9i] Update구문의 Returning Clause (0) | 2011.02.18 |