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

SYMPTOMS

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

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

DEBUG

[oracle@linuxvm01 dump]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> select SYS_CONTEXT(‘USERENV’, ‘CON_NAME’) NAME FROM DUAL;

NAME

—————————————————————————————-

CDB$ROOT

SQL> set pages 200

set lines 200

col directory_name format a20

col owner format a8

col ory_name format a13

col directory_path format a55

select * from dba_directories;

SQL> SQL> SQL> SQL> SQL> SQL>

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID

——– ——————– ——————————————————- ————-

SYS ORACLE_HOME / 1

SYS ORACLE_BASE / 1

SYS OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch 1

SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch 1

SYS OPATCH_INST_DIR /u01/app/oracle/product/12.1.0/db_1/OPatch 1

SYS DATA_PUMP_DIR /ade/b/1281484529/oracle/admin/seeddata/dpdump/ 1

SYS XSDDIR /u01/app/oracle/product/12.1.0/db_1/rdbms/xml/schema 1

SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/12.1.0/db_1/ccr/state 1

R

SYS XMLDIR /u01/app/oracle/product/12.1.0/db_1/rdbms/xml 1

SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/12.1.0/db_1/ccr/state 1

R2

CREATE DIRECTORY DMPDIR AS ‘/u01/app/oracle/dump’;

GRANT READ, WRITE ON DIRECTORY DMPDIR TO c##ogg_user;

select * from dba_directories;

SYS DMPDIR /u01/app/oracle/dump 1

select * from DBA_REGISTRY;

Solution

SQL> show parameter pool

NAME TYPE VALUE

———————————— ———– ——————————

buffer_pool_keep string

buffer_pool_recycle string

global_context_pool_size string

java_pool_size big integer 0

large_pool_size big integer 0

olap_page_pool_size big integer 0

shared_pool_reserved_size big integer 80530636

shared_pool_size big integer 0

streams_pool_size big integer 2G

SQL> alter system set streams_pool_size=4G scope=both;

SQL> show parameter stream

NAME TYPE VALUE

———————————— ———– ——————————

streams_pool_size big integer 4G

Work around: reboot db instance

Reference

http://www.dba-oracle.com/t_ora_31623_job_not_attached_to_session_via_specified_handle.htm

http://amit7oracledba.blogspot.com/2016/08/ora-31623-job-is-not-attached-to-this.html

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)

If not enough memory available:

SQL> alter system flush SHARED_POOL;

— OR

SQL> alter system flush BUFFER_CACHE;

SQL> alter system set streams_pool_size=128M ;

For AMM or ASMM, after data pump is complete, reset the parameter to leave system to manage this parameter again.

SQL> alter system reset streams_pool_size;

Leave a Reply

Your email address will not be published. Required fields are marked *