ORA-31626, (Database-pump)-job does not exist

Job Oprel 1

A short post about the ORA-31626 error. Found out a scheduled export- and import- job was broken in an Oracle Database. Error:  ORA-31626: job does not exist. After checking the scheduled jobs I realized that it’s not the scheduled job, but the datapump-job (should have read the error more carefully – it states ‘database pump job does not exists…. ).

And it seems all related to being cost-effective in the cloud….

The full error:

Error at line 2
ORA-20010: ORA-20010: ORA-31626: job does not exist –
ORA-06512: at “<user>.<program>”, line 169
ORA-06512: at line 1
ORA-06512: at “SYS.DBMS_ISCHED”, line 150
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 441
ORA-06512: at line 2
Script Terminated on line 2.

First it seemed a scheduled job, but it appeared to be a database-pump-job in a kind of ‘stale’ – situation.

Tue Jun 05 09:00:49 Central Europe Daylight Time 2018
Errors in file <path><file>53244.trc:
ORA-12012: error on auto execute of job 53244
ORA-20010: ORA-20010: ORA-39002: invalid operation – ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4882
ORA-06512: at “<owner>.<program>”, line 146
ORA-06512: at “<owner>.<program>”, line 169

What datapump jobs are there?

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM   dba_datapump_jobs;

The job was still there, but ‘not running’. And that’s the one which is causing the error.

Finding out the master table:

SELECT o.status, o.object_id, o.object_type, o.owner||’.’||object_name “OWNER.OBJECT” FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;

Drop this master table:

drop table <owner>.<object_name>;

And the job is running without problems.

But what causes this failure? Most likely: as we are running the database in the cloud, the server is being shut down at a regular interval when not being used to save costs. Not realizing that this job is scheduled around that time…..

So be aware of shutting servers down to save costs in the cloud.

Sources:

–> Note “How To Drop A NOT RUNNING DataPump Job? (Doc ID 1456784.1)

 

One thought on “ORA-31626, (Database-pump)-job does not exist

  1. Can you write it in straight English: what does object_name means in the query ‘drop table .;’?

    And the query below doesn’t work… does something has to be changed in it or what?

    I don’t get anything.

Leave a Reply

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

Next Post

The bare minimum of licensing an ODA X7-2

Facebook0TwitterLinkedinThe other day we had a discussion with Oracle sales about the question if you can buy an Oracle Database Appliance (ODA) X7-2 HA when you only have 1 socket license Oracle SE. The licensing rules can be interpreted different by some. Disregarding the question why you would buy an […]