:

ORA-31626: job does not exist

The Problem

We have a scheduled data pump job which runs every month to export an old partition of a table which we subsequently drop – it’s no longer needed in the application but we keep it as an export just in case we need to get the data back for some reason. Anyway, this job runs normally and we just change the name of it every month to reflect the name of the month we are exporting.

This month it failed because there was not enough space on the disk where we were exporting it to. We got the job failure alert through, cleared some space on the drive and then re-submitted the job. Unfortunately it failed again…This time there was enough space and there error message was:

ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at line 6

The Cause

Upon looking in DBA_DATAPUMP_JOBS there was a row which showed the failed job from the previous attempt:

Owner  Job_Name          Operation     Job_Mode     State
TEST   EXP_JUL11         EXPORT        TABLE        NOT RUNNING

This shows the data pump job which is NOT RUNNING and which is causing the next execution to fail because I am attempting to use the same job name, but you cannot have two jobs with the same name.

The Solution

You can just schedule a job with a different name and it will run fine. Here is the code I use to perform the data pump export using the DBMS_DATAPUMP API. Obviously you will have to change the names of the dump file, log file, directory and table name. I have put these variables in < > marks

declare
 l_dp_handle        NUMBER;
 begin
 l_dp_handle :=DBMS_DATAPUMP.OPEN (operation => 'EXPORT',
 job_mode         => 'TABLE',
 remote_link      => NULL,
 job_name         => '<EXP_JUL11v2>',
 VERSION          => 'LATEST'
 );
 DBMS_DATAPUMP.add_file    (handle         => l_dp_handle,
 filename       => '<JUL11.DMP>',
 DIRECTORY      => '<DPDIR>'
 ); 
 DBMS_DATAPUMP.add_file (handle         => l_dp_handle,
 filename       => '<JUL11.LOG>',
 DIRECTORY      => '<DPDIR>',
 filetype       => DBMS_DATAPUMP.ku$_file_type_log_file
 );
 DBMS_DATAPUMP.metadata_filter (handle      => l_dp_handle,
 NAME => 'NAME_EXPR',
 VALUE       => 'in(''<TESTTABLE>'')'
 );
 DBMS_DATAPUMP.data_filter    (handle      => l_dp_handle,
 NAME  => 'PARTITION_EXPR',
 VALUE       => 'in(''<JUL11>'')'
 ); 
 DBMS_DATAPUMP.start_job (l_dp_handle);
 DBMS_DATAPUMP.detach (l_dp_handle);
end;
/

If you really want to have the same job name then you can probably follow the instructions on this page which I found but I haven’t tested it. My job was on PROD and I didn’t want to do this just in case something funny happened and it was no bother to change the name of the job for me.

I have also read about people just bouncing the database to remove these orphaned rows from the database and the DBA_DATAPUMP_JOBS view but I am not sure if that will work for everyone.

In addition, there are articles which say to open an SR with Oracle support who will guide you in the direction of running the catproc.sql script. This might be necessary but I wouldn’t do it on my production server without testing it somewhere else and I’d still rather just rename the job than do this as it requires downtime.

Like it, share it...

Category: Data Pump


Related Posts

Leave a Reply

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