UDI-31623: operation generated ORACLE error 31623 / ORA-31623

 Scenerio
----------
 When doing an import ( impdp ) on Oracle 11g Rel.2 / RHEL 5.3 64bit
 -------------------------------------------------------------------------------------


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 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

Work around
---------------
 

 Increase the streams_pool_size from 0 to 16M or set the sga_target parameter.

SQL> alter system set streams_pool_size=40M ;

Also see MOSC note  308388.1 for details on diagnosing and tracing the ORA-31623 error. 


Source : Oracle metalink : DOC ID 308388.1
----------------------------------------------------------------------------------------------------------------

Error ORA-31623 When Submitting A DataPump Export Job (Doc ID 308388.1)

Applies to :

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.5 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 07-Jul-2014***

Symptoms :

When you are trying to take an export using DataPump utility you are getting the following errors:

UDE-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_DATAPUMP", line 2315

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3185

ORA-06512: at line 1

Cause : 

For submitting an export job with DataPump utility, queues, streams and Java objects are used. SQL tracing confirms that.
If some components are not valid in the database, then you will see these errors. By querying dba_registry you will find the different components and their status:

connect / as sysdba
SELECT comp_name, status, version
FROM   dba_registry;

Solution :

You can validate different components in the database by running the script $ORACLE_HOME/rdbms/admin/catpatch.sql via SQL*Plus:

spool catpatch.log

connect / as sysdba

shutdown immediate

startup migrate

@?/rdbms/admin/catpatch.sql

@?/rdbms/admin/utlrp.sql

SELECT comp_name, version, status

FROM dba_registry;

spool off

-- If you are on 10g Release 2, then perform:

connect / as sysdba

spool dictreload.log

startup restrict

alter system set shared_pool_size = 512M scope=spfile;

alter system set java_pool_size = 150M scope=spfile;

alter system set aq_tm_processes = 1 scope=spfile;

alter system set streams_pool_size = 10M scope=spfile;

alter system set cluster_database = false scope=spfile; -- If on RAC

shutdown immediate

startup upgrade

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/catupgrd.sql

spool off

alter system set cluster_database = true scope=spfile; -- If on RAC

shutdown immediate

startup

@?/rdbms/admin/utlrp.sql