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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.