ORA-31623: a job is not attached to this session via the specified handle
=====================
-Issue-
When performing Import via impdp, following error is coming:
ORA-31623: a job is not attached to this session via the specified handle
–Full error stack:
Import: Release 12.1.0.2.0 – Production on Wed Feb 2 11:08:25 2022
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
--Current Memory settings SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 256M SQL> SELECT name, ROUND(bytes/1024/1024) as MB, resizeable FROM V$SGAINFO ORDER BY bytes DESC; 2 3 NAME MB RESIZEABLE -------------------------------- ---------- -------------------- Maximum SGA Size 10368 No Shared Pool Size 6080 Yes Buffer Cache Size 3168 Yes Streams Pool Size 864 Yes Startup NUMA Shared Pool memory 448 No Shared IO Pool Size 416 Yes Startup overhead in Shared Pool 378 No Redo Buffers 132 No Free SGA Memory Available 32 Java Pool Size 32 Yes Large Pool Size 32 Yes Granule Size 32 No Fixed SGA Size 3 No In-Memory Area Size 0 No Data Transfer Cache Size 0 Yes SQL> SELECT name, ROUND(value/1024/1024) AS MB, description FROM V$PARAMETER WHERE name like '%pool%'; NAME MB DESCRIPTION ---------------------------------------- ---------- ---------------------------------------------- shared_pool_size 1568 size in bytes of shared pool large_pool_size 0 size in bytes of large pool java_pool_size 0 size in bytes of java pool streams_pool_size 256 size in bytes of the streams pool shared_pool_reserved_size 304 size in bytes of reserved area of shared pool buffer_pool_keep Number of database blocks/latches in keep buffer pool buffer_pool_recycle Number of database blocks/latches in recycle buffer pool global_context_pool_size Global Application Context Pool Size in Bytes olap_page_pool_size 0 size of the olap page pool in bytes 9 rows selected. SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 256M
=====================
Solution–
The solution was to increase the Streams Pool Size explicitly to 1Gb.
This specific system was running Several Golden Gate Replication Processes.
Although Stream Pool Size was set to 256M, and Oracle was supposed to allocate optimal memory for Stream Pool, the allocated memory by Oracle was obviously not enough.
After increasing the Stream Pool Size to 1Gb, still the error was not resolved as its required more streams_pool, the export finished after releasing the used streams from stopping OGG process in 4 minutes, without error.
if its small export/import then only you can pause the OGG process else increase the sufficient Streams_pool_size and start the export/import.
–Increase the streams_pool_size, In my case I am increasing it 1gb
SQL> alter system set streams_pool_size=1g scope=both sid='*'; alter system set streams_pool_size=1g scope=both sid='*' * ERROR at line 1: ORA-32018: parameter cannot be modified in memory on another instance -- do it on instance level from seperet for all instance. SQL> alter system set streams_pool_size=1g scope=both sid='instance1'; System altered. SQL> alter system set streams_pool_size=1g scope=both sid='instance2'; System altered. SELECT name, ROUND(bytes/1024/1024) as MB, resizeable FROM V$SGAINFO ORDER BY bytes DESC; NAME MB DESCRIPTION ------------------------------ ---------- -------------------------------------- shared_pool_size 1568 size in bytes of shared pool large_pool_size 0 size in bytes of large pool java_pool_size 0 size in bytes of java pool streams_pool_size 1024 size in bytes of the streams pool shared_pool_reserved_size 304 size in bytes of reserved area of shared pool buffer_pool_keep Number of database blocks/latches in keep buffer pool buffer_pool_recycle Number of database blocks/latches in recycle buffer pool global_context_pool_size Global Application Context Pool Size in Bytes olap_page_pool_size 0 size of the olap page pool in bytes 9 rows selected. set line 190 col NAME for a40 col DESCRIPTION for a80 SELECT name, ROUND(value/1024/1024) AS MB, description FROM V$PARAMETER WHERE name like '%pool%'; NAME MB DESCRIPTION ---------------------------------------- ---------- ----------------------------------- shared_pool_size 1568 size in bytes of shared pool large_pool_size 0 size in bytes of large pool java_pool_size 0 size in bytes of java pool streams_pool_size 1024 size in bytes of the streams pool shared_pool_reserved_size 304 size in bytes of reserved area of shared pool buffer_pool_keep Number of database blocks/latches in keep buffer pool buffer_pool_recycle Number of database blocks/latches in recycle buffer pool global_context_pool_size Global Application Context Pool Size in Bytes olap_page_pool_size 0 size of the olap page pool in bytes 9 rows selected. start the import and it will resolve the issue, if still issue is there, please stop the golden gate process for temporary resolve as integrated extract utilized streams_pool_size. Import: Release 12.1.0.2.0 - Production on Wed Feb 2 11:18:58 2022 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 Master table "SYSTEM"."SYS_IMPORT_FULL_08" successfully loaded/unloaded ************ ************ ************ ************ Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Job "SYSTEM"."SYS_IMPORT_FULL_08" successfully completed at Wed Feb 2 09:22:36 2022 elapsed 0 00:03:34