I recently tried importing metadata into a newly created database and received the following error.

Import: Release 12.1.0.2.0 – Production on Mon Jan 27 10:25:39 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

UDI-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 3905

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5203

ORA-06512: at line 1

After reading MOS note (Doc ID 1907256.1) I decided to check streams pool size.

SQL> show parameter streams

NAME                                 TYPE        VALUE

———————————— ———– ——————————

streams_pool_size                    big integer 0

SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE

———————————— ———– ——————————

memory_max_target                    big integer 0

SQL> show parameter memory_target

NAME                                 TYPE        VALUE

———————————— ———– ——————————

memory_target                        big integer 0

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

———————————— ———– ——————————

sga_max_size                         big integer 4G

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

———————————— ———– —————————––

sga_target                           big integer 4G

A quick check showed there was not enough space to increase the streams_pool_size.

SQL> alter system set streams_pool_size=200M sid=’orcl_1′ scope=memory;

alter system set streams_pool_size=200M sid=’orcl_1′ scope=memory

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

Since there was plenty of memory on the system, I decided to increase the sga size and streams_pool_size.

SQL> alter system set streams_pool_size=200M sid=’*’ scope=spfile;

SQL> alter system set sga_max_size=5G scope=spfile sid=’*’;

System altered.

SQL> exit

After recycling the instance the issue was resolved.

References:

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)