datapump consumes undo tablespace

Error ORA-30036 DataPump Import (IMPDP) Exhausts Undo Tablespace [ID 727894.1]

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
*** Checked for relevance on 06-OCT-2011 ***

Symptoms

The import DataPump session completes with the following errors:

ORA-31693: Table data object "[schema]"."[table-name]" failed to load/unload and is being skipped due to error:
ORA-30032: the suspended (resumable) statement has timed out
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
Job "[user]"."SYS_IMPORT_TABLE_01" completed with 141 error(s) at 01:15:34

This indicates that ROLLBACK was being performed during the time in which no progress was made. It appears there is excessive UNDO being generated.

Cause

Excess undo generation can occur when there is a Primary Key (PK) constraint present on the system. Import DataPump will perform index maintenance and this can increase undo usage especially if there is other DML occurring on the database.

Solution

Disable constraints for Primary Keys (PK) on the database during import datapump load. This will reduce undo as index maintenance will not be performed.