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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Priscila.

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

Last updated: March 18, 2024 - 3:54 am UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit

Viewed 50K+ 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 Connor 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

  (6 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

Another potential issue..

SJW, February 03, 2022 - 9:44 pm UTC

..the user doesn't have privilege/quota on the default tablespace
Connor McDonald
February 04, 2022 - 5:22 am UTC

Ah yes - good point that I'd forgotten to mention.

dbms_datapump.set_debug

Patrick Jolliffe, July 18, 2022 - 11:48 am UTC

For anyone else that comes here via Google (as I did) and would like some idea of how to troubleshoot this error in a more logical way, I just discovered this function (not in official documentation, but exposed in package body).
Output information is exposed in sql trace file.
Below shows redacted trace for the issue I am working on...

DECLARE
L_HANDLE NUMBER;
BEGIN

DBMS_DATAPUMP.SET_DEBUG(on_off=>1);
L_HANDLE := DBMS_DATAPUMP.ATTACH('<redacted>','<redacted>') ;
END;

SHDW: *** ATTACH call ***
SHDW: job_name = <redacted>
SHDW: job_owner = <redacted>
SHDW: ena_sec_roles = NULL

SHDW: Trapped to shadow error handling routine
SHDW: Top-level error number reported = -31626
SHDW: Shadow context error supplied = 0
SHDW: Error stack at time of error report:
SHDW: ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 421
ORA-31638: cannot attach to job <redacted> for user <redacted>
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 414
ORA-31632: master table "<redacted>.,<redacted>" not found, invalid, or inaccessible
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 406
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPV$FT_INT", line 2847
ORA-06512: at "SYS.KUPV$FT", line 210
SHDW: Backtrace stack:
SHDW: ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 421
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 414
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 406
ORA-06512: at "SYS.KUPV$FT_INT", line 2847
ORA-06512: at "SYS.KUPV$FT", line 210
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5315
SHDW: Call stack:
SHDW: ----- PL/SQL Call Stack -----
object line object
handle number name
0xec271c590 1840 package body SYS.DBMS_DATAPUMP.SHADOW_ERROR
0xec271c590 5327 package body SYS.DBMS_DATAPUMP.ATTACH
0xf419a1220 6 anonymous block
Connor McDonald
July 21, 2022 - 2:57 am UTC

thanks for dropping by Patrick

ORA-31626: job does not exist

vadim, March 12, 2024 - 9:00 pm UTC

ORA-31626: job does not exist
ORA-31633: unable to create master table "BLAH_BLAH.FOXTEST0226"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-01950: no privileges on tablespace 'FOX_APP_DATA'
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

Connor McDonald
March 18, 2024 - 3:54 am UTC

um.... your question?

More to Explore

Data Pump

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