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

Found out a scheduled export- and import job was broken. 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.

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? 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…..

Sources:

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

 

By |2018-06-27T22:06:29+00:00June 27th, 2018|Categories: Cloud, Database|Tags: |0 Comments

About the Author:

Leave A Comment

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