DataPump - Export - Import - 12.1 - ORA-31623: a job is not attached to this session via the specified handle
Exporting..... takes time in some case :)
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
If you are running export or import process
Option 1
expdp
DIRECTORY=data_pump_dir
FULL=Y
DUMPFILE=expdp_full_%U.dmp
LOGFILE=expdp_full_%U.log
PARALLEL=2
METRICS=y
LOGTIME=all
EXCLUDE=statistics
FLASHBACK_TIME=systimestamp
or
Option 2
expdp \"sys@SID as sysdba\"
directory=TMP
dumpfile=expdp.dmp
logfile=expdp.log
version=latest
metrics=yes
logtime=all
FULL=Y
Export: Release 12.1.0.2.0 - Production on Thu Aug 23 11:28:27 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics 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 3905
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5203
ORA-06512: at line 1
There are a different resolution of this issue and you have to try with first metalink or second - please read it and try to execute some of the recommendations like to unset NLS environments
set|grep NLS
NLS_DATE_FORMAT='dd.mm.yyyy hh24:mi:ss'
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
try to unset or set correct NLS_DATE_FORMAT as per information from the database - >
set linesize 1000
set pagesize 1000
col VALUE format A40
col PARAMETER format A40
select * from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
i try a lot of changes but without success and finally, resolution appear in metalink -> 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)
Errors are received at expdp or impdp using LOGTIME parameter
Information::
Data Pump export/import with LOGTIME parameter crashes if the environment variable NLS_DATE_FORMAT is set. In some cases, the errors are seen if both NLS_DATE_FORMAT and NLS_LANG are set.
The problem is addressed in
Bug 18920652 - DATAPUMP WITH LOGTIME CRASHES WHEN NLS_LANG IS SET AT O/S LEVEL
closed as a duplicate of
Bug 17714887 - ORA-31623 ON IMPDP WITH DBLINK
Please note that Bug 17714887 has been superseded by unpublished Bug 21094393
Unpublished Bug 21094393 is fixed in 12.2.
Resolution:
To solve the issue, use any of below alternatives:
Apply interim patch 21094393, if available for your platform and Oracle version.
To check for conflicting patches, please use the MOS Patch Planner Tool
Please refer to Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
If no patch exists for your version, please contact Oracle Support for a backport request.
- OR -
As possible workarounds:
Do not use the LOGTIME parameter, as it is not essential to the export/import functionality as such.
- OR -
Unset the NLS_DATE_FORMAT and NLS_LANG environment variables
In my case, i just remove parameter LOGTIME=all because I am not able to patch database :(
have fun with oracle