Wikipedia

Search results

ORA-31623: a job is not attached to this session via the specified handle




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)

No comments:

Post a Comment