ORA-31623: a job is not attached to this session via the specified handle - Below error while import was initiating:
Issue:-
Import: Release 11.2.0.4.0
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: /as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
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 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
Cause :-
Above issue can be caused if any registry component is invalid or Oracle is not able to provide sufficient memory to Datapump job.
Solution :-
Crosschecked the DBA _REGISTRY , all components were in VALID state.
Checked the SGA component allocated to database and saw that only SGA_MAX_TARGET is mentioned, SGA_TARGET is 0 . PFB below :-
Datapump use streams pool memory parameter to allocate memory to jobs, if SGA is manually sized then allocate some memory to streams_pool_size
SQL> alter system set streams_pool_size=128M scope=both;
IF ASMM/AMM is used then perform below steps :-
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1280M
sga_target big integer 0
Increased the value of SGA target to 4GB [varies based on your SGA size]
SQL> alter system set sga_max_size=4g scope=spfile;
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 4043309960 bytes
Database Buffers 201326592 bytes
Redo Buffers 28884992 bytes
Database mounted.
Database opened.
SQL> alter system set sga_target=4g;
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4G
sga_target big integer 4G
Tried import after this and it completed without any errors.
I hope this article help you
Thanks
Also, refer - 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)
