impdp ORA-04030 PASSWORD_HISTORY 的处理

导入100M的dmp，运行2分钟后报错ora-4030。
free -m 16G，sga 10g pga 2g
oracle 11.2.0.4单机

导入命令

impdp SCOTT/SCOTT directory=DIR1 dumpfile=A_1_cur.dmp   table_exists_action=replace
exclude="user:\"='SCOTT'\"" logfile=A_.log

Import: Release 11.2.0.4.0 - Production on Fri Sep 17 13:55:45 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_02":  SCOTT/******** directory=DIR1 dumpfile=A_1_cur.dmp table_exists_action=replace exclude=user:"='SCOTT'" logfile=A_.log
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 120048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2


报错日志：
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
mmap(offset=217747456, len=4096) failed with errno=12 for the file ora_dw01_orcl
DDE: Problem Key 'ORA 4030' was flood controlled (0x2) (incident: 296434)
ORA-04030: out of process memory when trying to allocate 16048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2

*** 2021-09-17 13:41:35.381
KUPP: Error 4030 detected in worker process DW01, worker id=2
OPIRIP: Uncaught error 27102. Error stack:
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 108
Additional information: 1441792
ORA-00448: normal completion of background process
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 16048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2

 关键trc文件（看alert.log里的提示）

more /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_296425/orcl_dw00_24864_i296425.trc
Dump file /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_296425/orcl_dw00_24864_i296425.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1
System name:	Linux
Node name:	DB1
Release:	2.6.32-504.el6.x86_64
Version:	#1 SMP Tue Sep 16 01:56:35 EDT 2014
Machine:	x86_64
VM name:	VMWare Version: 6
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 24864, image: oracle@DB1 (DW00)


*** 2021-09-17 13:39:50.572
*** SESSION ID:(13.15451) 2021-09-17 13:39:50.572
*** CLIENT ID:() 2021-09-17 13:39:50.572
*** SERVICE NAME:(SYS$BACKGROUND) 2021-09-17 13:39:50.572
*** MODULE NAME:(Data Pump Worker) 2021-09-17 13:39:50.572
*** ACTION NAME:(SYS_IMPORT_FULL_01) 2021-09-17 13:39:50.572

Dump continued from file: /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dw00_24864.trc
ORA-04030: out of process memory when trying to allocate 120048 bytes (session heap,kuxLpxAlloc)

========= Dump for incident 296425 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
96% 6444 MB, 562102 chunks: "kuxLpxAlloc               "                 <<<-----------妈呀，已经6G了！
session heap    ds=0x7f90cbce61a0  dsprt=0xc0d2140
3%  231 MB, 22075 chunks: "free memory               "
top uga heap    ds=0xc0d2140  dsprt=(nil)
0%   28 MB, 43334 chunks: "free memory               "
session heap    ds=0x7f90cbce61a0  dsprt=0xc0d2140
0% 6368 KB,  34 chunks: "free memory               "
pga heap        ds=0xc0cc6e0  dsprt=(nil)
0% 1936 KB,  89 chunks: "free memory               "
Alloc environm  ds=0x7f90cbd0e090  dsprt=0x7f90cbce61a0
0% 1781 KB,  33 chunks: "free memory               "
top call heap   ds=0xc0d1f20  dsprt=(nil)
0% 1647 KB,   7 chunks: "free memory               "
koh-kghu sessi  ds=0x7f90cb8a8b00  dsprt=0x7f90cbce61a0
0% 1006 KB,  20 chunks: "kpmalloc                  "
koh-kghu sessi  ds=0x7f90cb40c0f8  dsprt=0x7f90cbce61a0
0%  795 KB,  35 chunks: "static frame of inst      "  PL/SQL
koh-kghu sessi  ds=0x7f90cabb7b00  dsprt=0x7f90cbce61a0
0%  530 KB,  28 chunks: "recursive addr reg file   "  PL/SQL
koh-kghu sessi  ds=0x7f90cbbb1b08  dsprt=0x7f90cbce61a0

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
6724 MB total:
6485 MB commented, 237 KB permanent
239 MB free (7871 KB in empty extents),
6714 MB,   1 heap:    "session heap   "            231 MB free held

*** 2021-09-17 13:39:50.910
------------------------------------------------------
Summary of subheaps at depth 1
6481 MB total:
6453 MB commented, 193 KB permanent
28 MB free (47 KB in empty extents),
6471 MB, 562102 chunks:  "kuxLpxAlloc               " 28 MB free held

=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------

Dump of Real-Free Memory Allocator Heap [0x7f90cbcb6000]
mag=0xfefe0001 flg=0x5000003 fds=0x8 blksz=65536
blkdstbl=0x7f90cbcb6010, iniblk=111616 maxblk=524288 numsegs=156
In-use num=65389 siz=2756640768, Freeable num=0 siz=0, Free num=1 siz=10485760

==========================================

解决方案是 增加 exclude=PASSWORD_HISTORY
有一次在comments时遇到 Data Pump Worker 报错，也是exclude跳过，经常见的情况是statistics时报错。


参考：

ORA-39126：KUPW$WORKER.PUT_DDLS [PASSWORD_HISTORY] 中的worker意外的致命错误（文档 ID 2114832.1）