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

Oracle Data Pump
  • Oracle Version: Patchset 10
  • Platform: Windows Server 2003 R2

The Problem

I was attempting to execute a data pump import job which I had run numerous times before, but unfortunately this time around it didn’t seem to be working. I got the following error:

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

I knew that the syntax was correct because I had used the exact same syntax previously so it had to be something else specific to this instance.

The Cause

The cause was that I had increased the minimum size of the db_cache_size init.ora parameter which meant there was not enough memory available in the shared pool to handle the request.

The Solution

I reduced the db_cache_size parameter and it worked fine.

It is also possible that you may need to increase the size of the streams_pool_size, especially if you are using manual memory management and the value is set to 0.

SQL> alter system set streams_pool_size=128M scope=both;

In addition, you should check that the parameter aq_tm_processes is set to at least 1.

Why Make the Change…?

The reason for both of the above two changes is because in 11g the DB uses streams and advanced queuing.

If it still doesn’t work…

A further note on this is that I did all of the above and I still got the same issues. I found out that my SGA was not large enough.

I was using automatic memory management but I just think that there was too much pressure on the memory, i.e. lots of processes competing for SGA space and it meant that an export job was not able to be spawned.

I found this out because I restarted the DB and it worked without any change but 10 minutes later after all the processes started and people were using it more heavily it wouldn’t work again.

Just quick before you go…

Must Read Oracle DBA BooksHopefully that helped you out and resolved your Oracle error. While you’re here, what steps are you taking to improve your Oracle knowledge?

Check out my latest post about which Oracle books are a must read for any serious Oracle DBA. Learn from the best…

Like it, share it...

Category: Data Pump

Related Posts

Comments (2)

Trackback URL | Comments RSS Feed

  1. Bhaskar says:

    For me
    ERROR at line 1:
    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 4520
    ORA-06512: at line 61
    ORA-39001: invalid argument value

    this error is cause the sub-directory is missing from data pump DIR. Once the sub-dir is created the error is gone.

Leave a Reply to Robert Jackson Cancel reply

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