合 数据泵导入碰到UDI-31623和ORA-31623 a job is not attached to this session via the specified handle错误
Tags： Oracle故障处理数据泵ORA-31623UDI-31623
👉 本文共约3081个字，系统预计阅读时间或需12分钟。
UDI-31623和ORA-31623: a job is not attached to this session via the specified handle
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | d:\>impdp system/"lhr"@127.0.0.1/jde DIRECTORY=D1 dumpfile=JDE92.DMP FULL=Y EXCLUDE=STATISTICS table_exists_action=REPLACE Import: Release 19.0.0.0.0 - Production on Wed Jun 15 20:45:56 2022 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production 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 4747 ORA-06512: at "SYS.KUPV$FT_INT", line 2144 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.KUPV$FT_INT", line 2081 ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496 ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127 ORA-06512: at line 1 |
分析
1、查看告警日志是否有啥错误信息输出
2、分析streams_pool_size是否太小，最少配置128M
解决
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 0 SQL> alter system set streams_pool_size=256m ; System altered. SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 256M SQL> show parameter pool NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_keep string buffer_pool_recycle string java_pool_size big integer 4M large_pool_size big integer 0 memoptimize_pool_size big integer 0 olap_page_pool_size big integer 0 shared_pool_reserved_size big integer 27053260 shared_pool_size big integer 1G streams_pool_size big integer 256M SQL> select * from v$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 9336696 No 0 Redo Buffers 7438336 No 0 Buffer Cache Size 4093640704 Yes 0 In-Memory Area Size 0 No 0 Shared Pool Size 1908408320 Yes 0 Large Pool Size 0 Yes 0 Java Pool Size 4194304 Yes 0 Streams Pool Size 268435456 Yes 0 Shared IO Pool Size 0 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 4194304 No 0 Maximum SGA Size 6291453816 No 0 Startup overhead in Shared Pool 390813544 No 0 Free SGA Memory Available 0 0 14 rows selected. |
另外，shared_pool_size参数也需要配置大一点，否则可能会报如下的错误：
1 2 3 4 | ORA-31693: Table data object "DV"."F9K" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-39776: fatal Direct Path API error loading table "DV"."F9K" ORA-04031: unable to allocate 13224 bytes of shared memory ("shared pool","unknown object","KTSL subheap","ktsl_load_disp-2") |
参考
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)
GOAL
This document explains how to resolve the following errors during an Export DataPump (expdp) or Import DataPump job (impdp).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | $ 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 ... |
There are several possible reasons why a Data Pump cannot be started. Each root cause has its own solution.
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
