Data Pump Import Encountering ORA-04030 (session heap,kuxLpxAlloc)

Recently we faced this issue while importing a schema of size around 900 GB.

It started well but when it started importing the statistics of table it failed with the below error:

Errors in file /u01/app/oracle/diag/rdbms/dmstr/DMSTR/trace/DMSTR_dw00_21842.trc (incident=96666):

ORA-04030: out of process memory when trying to allocate 120048 bytes (session heap,kuxLpxAlloc)

Incident details in: /u01/app/oracle/diag/rdbms/dmstr/DMSTR/incident/incdir_96666/DMSTR_dw00_21842_i96666.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Dumping diagnostic data in directory=[cdmp_20121220052220], requested by (instance=1, osid=21842 (DW00)), summary=[incident=96666].

Thu Dec 20 05:22:21 2012

Errors in file /u01/app/oracle/diag/rdbms/dmstr/DMSTR/trace/DMSTR_dw00_21842.trc (incident=96667):

ORA-04030: out of process memory when trying to allocate 120048 bytes (session heap,kuxLpxAlloc)

ORA-06512: at "SYS.KUPW$WORKER", line 1838

ORA-06512: at line 2

Incident details in: /u01/app/oracle/diag/rdbms/dmstr/DMSTR/incident/incdir_96667/DMSTR_dw00_21842_i96667.trc

Symptoms: During data pump import (impdp) of large database which contains large partitioned tables and many sub-partitions, the import will fail with ORA-4030 errors.

Solution:

We used EXCLUDE=STATISTICS in the import command and it solved the issue.

 Note: After  import we need to gather the statistics because it was not imported.