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

Job Oprel 2
0 0
Read Time:2 Minute, 2 Second

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)

 

About Post Author

Job Oprel

Until February 2019, Job worked as a solution architect at AMIS Services with a special interest in Oracle licensing, High Availability architectures and managing complex (Oracle) environments, which includes Cloud environments.With a background as Oracle developer, DBA, team-manager and license-consultant he is able to utilize the Oracle technologies to a cost-efficient architecture for his customers.He is regularly involved in consultancy regarding: - Unlimited License Agreements (ULA). - License compliancy-checks and advice regarding optimizing the environment. - Second opinions. - Education / presentations about licensing and managing your infrastructure in the most cost-efficient manner.Twitter: @jobaclenl
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts 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.

    1. To Job Oprel : This was helpful.
      To Vladimir : It is in English bro, object_name will have different values in different worlds.

Comments are closed.

Next Post

The bare minimum of licensing an ODA X7-2

The 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 […]
%d bloggers like this: