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. And it’s 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)