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

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.15.0.0

 

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

19c 버전에서 expdp 시 ORA-31623 에러 발생

$ expdp system/"We1come$" directory=locktest job_name=LOCKTEST_01 dumpfile=expdp_locktest1.dmp logfile=expdp_locktest1.log tables=imsi.locktest status=60
 
Export: Release 19.0.0.0.0 - Production on Fri Aug 17 21:33:01 2023
Version 19.15.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
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 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

 

 

해결 방법 : stream pool size를 할당 또는 증가

stream pool size 확인

SQL> show parameter streams_pool
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

streams_pool_size가 0임

 

 

sga 파라미터 확인

SQL> show parameter sga_
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 3G
sga_target                           big integer 0 <<<<<<
 
SQL> show parameter memory_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 0 <<<<<< 

sga_target와 memory_target 파라미터가 모두 0 임
이 뜻은 현재 db 메모리를 manual 로 관리 하고 있다는 뜻

 

 

sga info 확인

SQL> select * from v$sgainfo;
 
NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size                      8929408 No           0
Redo Buffers                        7843840 No           0
Buffer Cache Size                 268435456 Yes          0
In-Memory Area Size                       0 No           0
Shared Pool Size                  411041792 Yes          0
Large Pool Size                           0 Yes          0
Java Pool Size                      4194304 Yes          0
Streams Pool Size                         0 Yes          0
Shared IO Pool Size                       0 Yes          0
Data Transfer Cache Size                  0 Yes          0
Granule Size                        4194304 No           0
Maximum SGA Size                 3221221504 No           0
Startup overhead in Shared Pool   297519200 No           0
Free SGA Memory Available        2520776704              0

 

 

streams_pool_size 소량 할당

SQL> alter system set streams_pool_size=64M scope=both sid='*';
 
System altered.

 

 

expdp 재실행

# expdp system/"We1come$" directory=locktest job_name=LOCKTEST_01 dumpfile=expdp_locktest1.dmp logfile=expdp_locktest1.log tables=imsi.locktest status=60
 
Export: Release 19.0.0.0.0 - Production on Fri Aug 17 21:36:49 2023
Version 19.15.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
Job: LOCKTEST_01
  Operation: EXPORT
  Mode: TABLE
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 0
  Dump File: /home/oracle/datapump/expdp_locktest1.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Instance ID: 0
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Wednesday, 00 Sat, 0000 0:00:00
  State: UNDEFINED
Starting "SYSTEM"."LOCKTEST_01":  system/******** directory=locktest job_name=LOCKTEST_01 dumpfile=expdp_locktest1.dmp logfile=expdp_locktest1.log tables=imsi.locktest status=60
(정상 동작)

정상적으로 동작함

 

 

원인 : stream pool 미할당(부족)으로 인한 에러 발생

db 메모리를 메뉴얼로 관리함에 따라 stream pool size 할당이 되어 있지 않아 datapump 가 미동작함
datapump 사용시 stream pool 이 필요함

 

 

참조 : 

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)
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/STREAMS_POOL_SIZE.html#GUID-3FFF66CB-5A1E-43AF-B30A-A8E189BFF3FF
https://ittutorial.org/ora-31623-a-job-is-not-attached-to-this-session-via-the-specified-handle/