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

SYMPTOM

The below errors occurred while export tables by using datapump:

$expdp userid=... tables=... directory=... dumpfile=...
..

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

CAUSES

Stream pool size is too small, and there is no more memory for stream pool to increase.

SOLUTION

Increase streams_pool_size:

SQL> show parameter streams_pool_size

NAME              TYPE        VALUE
----------------- ----------- ---------
streams_pool_size big integer 20M

SQL> alter system set streams_pool_size=128M ;

If not enough memory available:

SQL>  alter system flush SHARED_POOL;
System altered.

-- OR 

SQL> alter system flush BUFFER_CACHE;
System altered.

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;

How To Flush an Object Out the Library Cache

We know how to flash all shared pool by :

SQL>alter system flush shared_pool;

Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.

Subscribe to get access

Read more of this content when you subscribe today.