expdp 报错UDE-31623 ORA-31623

今天，在一个很小的库上导出一个用户的数据，报UDE-31623 ORA-31623错误。

参数文件如下：

userid='/as sysdba'
directory=dump
dumpfile=HN_CODE.dmp
logfile=HN_CODE.log
schemas=HN_CODE

详细的报错如下：

Export: Release 12.2.0.1.0 - Production on Thu Nov 25 23:48:57 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4750
ORA-06512: at "SYS.KUPV$FT_INT", line 1872
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT_INT", line 1805
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2266
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4498
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6104
ORA-06512: at line 1

之前没见过这个错误，首先在mos上搜索看看，很快找到了如下的一篇文档：

How to resolve the Data Pump error ORA-31623 UDE-31623 (a job is not attached to this session via the specified handle) ? (Doc ID 1907256.1)

这篇文档是专门针对该错误的处理步骤，大概扫了一眼，总共有7步，也就是说该错误总共有7种可能性。

我相信Oracle的专业性，第一种一定是最常见的、可能性最大的：

Step 1. First check the value for the STREAMS_POOL_SIZE in the database:
connect / as sysdba

show parameter streams_pool
select * from v$sgainfo;
...
 Streams Pool Size                               0 Yes

If the STREAMS_POOL_SIZE is too small, then a Data Pump job will fail. This can also happen when using Automatic Shared Memory Management (ASMM), or Automatic Memory Management (AMM) and there is not sufficient memory to increase the STREAMS_POOL_SIZE.
Manual settings for the STREAMS_POOL_SIZE of 64M, 128M or even to 256M have proven to be successful.
Also increase sga_target (for ASMM) or memory_target (for AMM) to have more free memory available during automatic tuning of the SGA components.

To avoid this DataPump error, you will need to configure the database with some Streams Pool.
Manually set the STREAMS_POOL_SIZE (using ALTER SYSTEM or by changing the value in the the PFILE/SPFILE), re-start the database and re-attempt the Data Pump Export.

意思就是streams_pool太小了，查了下该库，总内存不大（12000M），streams pool为0，那么很大可能就是它的问题。

SQL> show parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 12000M
sga_min_size                         big integer 0
sga_target                           big integer 12000M
unified_audit_sga_queue_size         integer     1048576
SQL> select * from v$sgainfo;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size                     12173600 No           0
Redo Buffers                       21377024 No           0
Buffer Cache Size                5905580032 Yes          0
In-Memory Area Size                       0 No           0
Shared Pool Size                 6442450944 Yes          0
Large Pool Size                   167772160 Yes          0
Java Pool Size                     33554432 Yes          0
Streams Pool Size                         0 Yes          0
Shared IO Pool Size               536870912 Yes          0
Data Transfer Cache Size                  0 Yes          0
Granule Size                       33554432 No           0
Maximum SGA Size                 1.2583E+10 No           0
Startup overhead in Shared Pool   488721368 No           0
Free SGA Memory Available                 0              0

14 rows selected.

尝试调大，报内存不足。回头一想，因为本身就是SGA自动管理，如果能调成功，SGA自动管理也能实现，所以报错就不难理解了。

SQL> alter system set streams_pool_size=64M;
alter system set streams_pool_size=64M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

这时候可以考虑增大SGA，但是增大SGA相对较麻烦，可能还需要调整hugepage，同时要重启数据库。既然都重启数据库了，那内存全部释放了，无需增大也应该可以设置streams_pool_size参数。想来想去，还不如直接手工刷内存（在维护期间）：

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> alter system set streams_pool_size=64M;

System altered.

调完后重新导出，一切顺利！