May 10, 2021
I got ” ORA-31623: a job is not attached to this session via the specified handle ” error in Oracle database.
ORA-31623: a job is not attached to this session via the specified handle
Details of error are as follows.
ORA-31623: A job is not attached to this session via the specified handle Cause: An attempt to reference a job using a handle which is invalid or no longer valid for the current session. Action: Select a handle corresponding to a valid active job or start a new job.
$ expdp system/<PASSWORD> DIRECTORY=<directory_name> DUMPFILE=<dmp_name>.dmp LOGFILE=<log_name>.log FULL=y Export: Release 11.2.0.1.0 - Production on Thu Jun 19 13:14:32 2014 Copyright 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 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 3263 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488 ORA-06512: at line 1 -- or: -- 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_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583 ORA-06512: at line 1 -- or: -- UDI-00008: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583 ORA-06512: at line 1 ...
A job is not attached to this session via the specified handle
This ORA-31623 error is related with the An attempt to reference a job using a handle which is invalid or no longer valid for the current session.
To solve this error, set streams_pool_size to 64M as follows.
Alter system set streams_pool_size
SQL> show parameter streams_pool_size NAME TYPE VALUE ———————————— ———– —————————— streams_pool_size big integer 0 SQL> alter system set streams_pool_size=64M scope=both sid='*'; System altered.
Use the steps below one by one to address and fix this issue:
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.
Step 2. Check for any possible invalid Data Pump queue objects:
connect / as sysdba show parameter aq col owner for a10 col object_name for a30 analyze table kupc$datapump_quetab validate structure cascade; analyze table kupc$datapump_quetab_1 validate structure cascade; select object_id, owner, object_name, status from dba_objects where object_name like 'KUPC$DATAPUMP_QUETAB%'; set lines 100 col status for a9 col object_type for a20; col owner.object for a50 select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT" from dba_objects where object_name like '%DATAPUMP_QUETAB%' order by 3,4;
If there are any invalid queue objects, then a Data Pump job will fail. This usually also results in the following error in the alert.log file:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], [] For details and full resolution, see: Note 754401.1 - Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import
Step 3. Check for any invalid registry components (CATALOG, CATPROC and JAVAVM), and invalid sys owned objects:
connect / as sysdba set lines 90 col version for a12 col comp_id for a8 col schema like version col comp_name format a35 col status for a12 select comp_id,schema,status,version,comp_name from dba_registry order by 1; set lines 120 col status for a9 col object_type for a20; col owner.object for a50 select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT" from dba_objects where status != 'VALID' and owner='SYS' and object_name not like 'BIN$%' order by 4,2;
If the registry components CATALOG, CATPROC and/or JAVAVM, and/or objects like SYS.KUPW$WORKER or SYS.KUPP$PROC are invalid, then a Data Pump job will likely fail.
To resolve this problem, reload Data Pump in the database:
connect / as sysdba -- Start spooling to file: spool catproc.out set lines 120 numwidth 12 pages 10000 long 2000000000 alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; show user select sysdate from dual; shutdown immediate -- for 9.2, use: startup migrate startup migrate @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/rdbms/admin/utlrp.sql spool off spool registry.out -- Registry status: set lines 90 col version for a12 col comp_id for a8 col schema like version col comp_name format a35 col status for a12 select comp_id,schema,status,version,comp_name from dba_registry order by 1; -- Invalid objects: set lines 120 col status for a9 col object_type for a20; col owner.object for a50 select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT" from dba_objects where status != 'VALID' and owner='SYS' and object_name not like 'BIN$%' order by 4,2; shutdown immediate startup spool off
For details and references, see:
Note 430221.1 – How To Reload Datapump Utility EXPDP/IMPDP
Note 863312.1 – Best Practices for running catalog, catproc and utlrp script
Note 308388.1 – Error ORA-31623 When Submitting A DataPump Export Job
In case JAVAVM component is invalid, validate it using the steps from:
Note 1112983.1 – How to Reload the JVM in 11.2.0.x
Note 276554.1 – How to Reload the JVM in 10.1.0.X and 10.2.0.X
Note 1612279.1 – How to Reload the JVM in 12.1.0.x
and/or create a Java SR if more help is needed.
Step 4. Check if parameter _FIX_CONTROL is set for Bug 6167716:
connect / as sysdba show parameter _fix_control
If this hidden parameter is set, then a Data Pump job will fail.
For details and full resolution, see:
Note 1150733.1 – DataPump Export (EXPDP) Fails With Errors ORA-31623 ORA-6512 If Parameter _FIX_CONTROL=’6167716:OFF’ Has Been Set
Step 5. If the Data Pump job is started through a package, check if the package was created with invoker’s right (AUTHID clause):
connect / as sysdba
set lines 120 numwidth 12 pages 10000 long 2000000000
col ddl for a100
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<SCHEMA_NAME>') "DDL" from dual;
If the package was created with an invoker’s right, then a Data Pump job will fail when started through this package.
For details and full resolution, see:
Note 1579091.1 – DataPump Job Fails With Error ORA-31623 A Job Is Not Attached To This Session Via The Specified Handle
Step 6. If the Data Pump job is started in DBConsole / OEM, and the job is selected to be re-run (or you want to edit the job), then the Data Pump job will fail and following errors will be reported:
ERROR: No data pump job named "jobname" exists in the database ORA-31623: a job is not attached to this session via the specified handle Execute Failed: ORA-31623: a job is not attached to this session via the specified handle ORA-6512: at "SYS.DBMS_DATAPUMP", line 2315 ORA-6512: at "SYS.DBMS_DATAPUMP", line 3157 ORA-6512: at line 27 (DBD ERROR: OCIStmtExecute) -- or -- Edit is not supported for this job type, only general information
For details and full resolution, see:
Note 788301.1 – Error ORA-31623 On DataPump Export Via DBScheduler After First Run Was Successful
Note 461307.1 – How To Export Database Using DBConsole/OEM In 10G
Step 7. If parameter LOGTIME is being used, Data Pump export/import with LOGTIME parameter crashes if the environment variable NLS_DATE_FORMAT is set.
For details and full resolution, see:
Note 1936319.1 – Data Pump Export Or Import Throws ORA-31623 When Using LOGTIME Parameter
Step 8. Running a remote DataPump job against Oracle 12.1.0.2 database, the export can fail with ORA-31623. Database alert.log file reports ORA-0600 [ksfdcmtcre4], [KGNFS SERVER REBOOT] error.
The incident trace file shows the following information:
Dump continued from file: <ADR_base>/diag/product_type/product_id/instance_id>/trace/<SID>_dm00_<INCIDENT_NUMBER>.trc
[TOC00001]
ORA-00600: internal error code, arguments: [ksfdcmtcre4], [KGNFS SERVER REBOOT], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE_INT", line 79
ORA-06512: at "SYS.KUPF$FILE", line 2151
ORA-06512: at "SYS.KUPF$FILE", line 1473
...
Stack Trace: ... kkgereml kuppChkErr kupprdp opirip opidrv sou2o ...
DNFS is enabled by default when installing Oracle. When an alternative storage vendor is used, there is a conflict causing the ORA-0600 [ksfdcmtcre4], [KGNFS SERVER REBOOT] error preventing tasks to be completed successfully. In this case, the task was EXPDP job.
To prevent the error, disable DNFS, since it is not being used.
For details, see Note 954425.1 – Direct NFS: FAQ.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,283 views last month, 1 views today
About Mehmet Salih Deveci
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.
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)
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
Содержание
- Datapump Expdp fails with the errors: UDE-31623: Operation Generated ORACLE Error 31623 and ORA-31623: a job is not attached to this session via the specified handle (Doc ID 2685386.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- 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)
- Applies to:
- Solution
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- Oracle Database Blog
- oracleops-support
- Wikipedia
- 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:
- Oracle 11.2 Datapump throwing UDI-31623 error
Datapump Expdp fails with the errors: UDE-31623: Operation Generated ORACLE Error 31623 and ORA-31623: a job is not attached to this session via the specified handle (Doc ID 2685386.1)
Last updated on JULY 09, 2020
Applies to:
Symptoms
Data Pump Export
When attempting to export using these parameters
DIRECTORY = DATA_PUMP_DIR
DUMPFILE =Table1.dmp
LOGFILE=Table1_Exp.log
TABLES = Table1_table
the following error occurs.
ERROR
————————
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 3905
ORA-06512: at «SYS.DBMS_DATAPUMP», line 5203
ORA-06512: at line 1
Changes
Database PSU and JavaVM PSUВ had just been installed.
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | 
| 
| 
| Legal Notices | Terms of Use
Источник
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)
Last updated on DECEMBER 12, 2022
Applies to:
This document explains how to resolve the following errors during an Export DataPump (expdp) or Import DataPump job (impdp).
DIRECTORY= DUMPFILE= .dmp LOGFILE= .log FULL=y
Export: Release 11.2.0.1.0 — Production on Thu Jun 19 13:14:32 2014
Copyright В 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 — Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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 3263
ORA-06512: at «SYS.DBMS_DATAPUMP», line 4488
ORA-06512: at line 1
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_SYS_ERROR», line 79
ORA-06512: at «SYS.DBMS_DATAPUMP», line 1137
ORA-06512: at «SYS.DBMS_DATAPUMP», line 4583
ORA-06512: at line 1
UDI-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at «SYS.DBMS_SYS_ERROR», line 79
ORA-06512: at «SYS.DBMS_DATAPUMP», line 1137
ORA-06512: at «SYS.DBMS_DATAPUMP», line 4583
ORA-06512: at line 1
.
There are several possible reasons why a Data Pump cannot be started. Each root cause has its own solution.
Solution
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
Oracle Database Blog
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=’*’;
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)
Источник
oracleops-support
Quick reference for database operation support !
Wikipedia
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:
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;
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
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)
Источник
Oracle 11.2 Datapump throwing UDI-31623 error
I have iniciated a datapump import and realized that I gave a wrong tablespace name while remapping, then terminated the session and came out of impdp. but later when I tried to do import again I am getting below error. now even expdp and impdp both are throwing the below error. Please help me out here.
impdp REMAP_TABLESPACE=CASE1705:IGNITE50 REMAP_SCHEMA=CASE1705:IGNITE55 directory=data_pump_dir DUMPFILE=MIG_DP_IGNITE_20110126.DMP log=imp_ignite55_20110131.log
Import: Release 11.2.0.1.0 — Production on Mon Jan 31 12:54:56 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 3263
ORA-06512: at «SYS.DBMS_DATAPUMP», line 4488
ORA-06512: at line 1
Here is the alert log entry
Mon Jan 31 12:55:10 2011
Errors in file /ORACLE/app/oracle/diag/rdbms/esbdev/ESBDEV/trace/ESBDEV_ora_725058.trc (incident=134948):
ORA-00600: internal error code, arguments: [kqlInvObj:user], [162], [], [], [], [], [], [], [], [], [], []
Incident details in: /ORACLE/app/oracle/diag/rdbms/esbdev/ESBDEV/incident/incdir_134948/ESBDEV_ora_725058_i134948.trc
Mon Jan 31 12:55:40 2011
Trace dumping is performing > Mon Jan 31 12:55:45 2011
Sweep [inc][134948]: completed
Sweep [inc2][134948]: completed
Источник
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)
Today I was doing an import and got below error while import was initiating :-
Issue :-
Import: Release 11.2.0.4.0 — Production on Thu Aug 4 22:49:29 2016
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
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 helped you
Thanks
Amit Rath