Skip to Main Content
  • Questions
  • Getting ORA-31626: job does not exist and ORA-06512 from my export data pump job

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priscila.

Asked: January 18, 2017 - 10:34 am UTC

Last updated: May 03, 2021 - 4:42 am UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Hello Tom,

Please, I need a big help.

I have a job scheduled in Enterprise Manager 12c. This job do a full export, but two days ago I started to get the error below:

ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at line 12

This is my script:

WHENEVER SQLERROR EXIT FAILURE;
declare
h1 NUMBER;
data date := sysdate;
v_file_name varchar2(30);
flashback number;
v_job varchar2(30);
v_log varchar2(30);
begin
v_log :='expdatcm_'||to_char(sysdate,'dd-mm-yyyy')||'.log';
select current_scn into flashback from v$database;
v_file_name := 'expdatcm_'||to_char(sysdate,'dd-mm-yyyy')||'_%%U.dmp';
h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'FULL', version => 'COMPATIBLE',job_name=>'EXP_FULL_CM5');
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename =>v_log , directory => 'EXPDAT', filetype => 3);
dbms_datapump.add_file(handle => h1, filename => v_file_name, directory => 'EXPDAT', filetype => 1, filesize => '4G');
dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_SCN', value => flashback);
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.detach(handle => h1);
end;
/



These are my user grants:

GRANT "CONNECT" TO "TESTE"
GRANT "DBA" TO "TESTE"


GRANT ADMINISTER SQL TUNING SET TO "TESTE"
GRANT ADVISOR TO "TESTE"
GRANT SELECT ANY DICTIONARY TO "TESTE"
GRANT ANALYZE ANY TO "TESTE"
GRANT EXECUTE ANY PROCEDURE TO "TESTE"
GRANT SELECT ANY TABLE TO "TESTE"
GRANT UNLIMITED TABLESPACE TO "TESTE"
GRANT ALTER SESSION TO "TESTE"
GRANT ALTER SYSTEM TO "TESTE"


GRANT EXECUTE ON "SYS"."SYS_PLSQL_7945_835_1" TO "TESTE" WITH GRANT OPTION
GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "TESTE"
GRANT EXECUTE ON "SYS"."DBMS_WORKLOAD_REPOSITORY" TO "TESTE"


What could be wrong?

Thank you

and we said...

Your script looks fine to me - I ran it on my machine and it worked without problems, so it doesn't look like any kind of inherent software bug.

There's a number of possbilities. First make sure the required grants are done directly (not via role), so try this first:

grant create session, create table, create procedure, exp_full_database, imp_full_database to TESTE;
grant read, write on directory EXPDAT to TESTE;

If that doesnt work, then make sure

- aq_tm_processes is greater than zero
- streams_pool_size is greater than zero

since datapump uses these facilities to work

If that doesnt work, then check the alert log for any other errors that may be occurring, and if all else fails, get in touch with Support.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Grants worked for me

A reader, February 23, 2017 - 3:14 pm UTC

I was recently having a similar issue for an export that previously worked. Running the grant similar to this is what fixed my issue. Thanks!

grant create session, create table, create procedure, exp_full_database, imp_full_database to TESTE;
Connor McDonald
February 25, 2017 - 12:58 am UTC

Glad we could help

One more possible cause

Ralf K├Âlling, October 18, 2020 - 10:18 am UTC

Another cause for this error I found out today:
The behavior also comes up if the master table of your datapump export exists already (in my case because I shut down the virtual box with the database when the hourly export was just running ...). After I located the table with
SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name 
FROM dba_objects o, dba_datapump_jobs j 
WHERE o.owner=j.owner_name
AND o.object_name=j.job_name; 

and dropped it everything worked fine again (Thanks Job Oprel for the hint!!!)

Connor McDonald
October 19, 2020 - 2:32 am UTC

Thanks for stopping by and adding the information

additional advice

Nicole Pasikowski, April 30, 2021 - 5:36 pm UTC

This was helpful but did not entirely solve my issue. Here are two other items:


alter user teste quota unlimited on users; (or whatever tablespace is their default)
grant execute on dbms_metadata to teste; (necessary if it has been revoked from public)

Also from sqlplus with test account try:
1. creating a table
2. inserting into the table
3. try running expdp on the command line

This helped me flush out the problems I was having.

Connor McDonald
May 03, 2021 - 4:42 am UTC

Good input

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.