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…..
–> Note “How To Drop A NOT RUNNING DataPump Job? (Doc ID 1456784.1)