Neil Chandler's DBA Blog

Yesterday I had a problem… I noticed that one of our overnight jobs was failing. The job in question is an ad-hoc tidy-up job, ran as needed when a performance metric for a specific query indicates that we’re going to be in trouble soon.

So, what was the problem? I have the stack trace captured in the job output:

Finished shrinks at 02-APR-2012 00:12
 Creating job data pump export job LOG_EXPORT
 User:....... NEW_SCHEMA
 Tables:..... 'table-1','table-2'
 DECLARE
 *
 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 911
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
 ORA-06512: at line 22

So, “job does not exist”! What does that mean, and what could be causing this to happen? What has changed? Let’s look at the code at line 22 and see what we’re running:

.
.
v_handle := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', 
                                 job_mode  => 'SCHEMA', 
                                 job_name  => 'LOG_EXPORT');
.
.

And that’s it. We’re initiating a DBMS_DATAPUMP job (to extract the data to an external file before manipulation.)

So, what has changed? Since the last invokation of this job, we have had a new software release, and for this product that means a whole new schema. We’ve missed something. We must have a permissions problem within the database. Fortunately, we keep the old schema in place, locked, following an upgrade so all we need to do is compare the permissions for the old and new schemas and we’re done.

select granted_role from dba_role_privs where grantee = 'OLD_SCHEMA'
minus
select granted_role from dba_role_privs where grantee = 'NEW_SCHEMA'
/

no rows selected

select privilege from dba_sys_privs where grantee = 'OLD_SCHEMA'
minus
select privilege from dba_sys_privs where grantee = 'NEW_SCHEMA'
/

no rows selected

select table_name,privilege from dba_tab_privs where grantee = 'OLD_SCHEMA'
minus
select table_name,privilege from dba_tab_privs where grantee = 'NEW_SCHEMA'
/

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
DATA_PUMP_DIR                  READ
DATA_PUMP_DIR                  WRITE

Ah ha! There’s the difference! And it is definitely data pump related, so let’s run the grants into the database and run the job again. This will fix it.

grant READ  on DIRECTORY data_pump_dir to NEW_SCHEMA;
grant WRITE on DIRECTORY data_pump_dir to NEW_SCHEMA;

... and run the job again...

 DECLARE
 *
 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 911
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
 ORA-06512: at line 22

What! Oh nuts. Why didn’t that work? [because you have not assigned a directory to DBMS_DATAPUMP yet! It'll be useful later though... Now stop jumping to conclusions! ed.] What are we going to do now? We know the permissions match. I’ve checked the O/S and there’s space in the filesystem. DBMS_DATAPUMP is not giving me enough information; what can I do to diagnose this problem further? I know! Why don’t I use datapump externally and see if that makes a difference…

expdp NEW_SCHEMA/..pwd..@..DB.. 
 directory=data_pump_dir 
 dumpfile=neilchandler.dmp 
 job_name=neilchandler

Export: Release 10.2.0.3.0 - 64bit Production on Tuesday, 03 April, 2012 12:54:27
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31626: job does not exist
ORA-31633: unable to create master table "NEW_SCHEMA.NEILCHANDLER"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'

Oh heck, we can’t write the parameter table as we have been denied a space quota on the default tablespace. D’oh! How painful was that to locate?

alter user NEW_SCHEMA quota unlimited on USERS;

And it all worked.

My question is, why couldn’t DBMS_DATAPUMP tell me that?

Did you know that Datapump creates a table on the fly? I had forgotten, but I suspect I won’t forget again after resolving a datapump failure.

We do a datapump export every night. Tonight it failed. Last night we had a kernel panic which caused server reboot at the same time as the usual datapump job was running, and so it didn’t clean itself up properly. So tonights job was failing as follows:

expdp userid='/ as sysdba' estimate_only=n status=0
      parallel=3 directory=export_dir job_name=JOBNAME
      dumpfile=ORASID.dmp logfile=ORASID.log FULL=y

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 24 March, 2011 5:15:36

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.JOBNAME"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-00955: name is already used by an existing object

The key line is highlighted in red - an ORA-00955. The expdp creates a “temporary” table into which it writes lots of information about the export, which it removes at the end of the job.

If you look in the schema which you are using to perform the datapump, you will find a table with the same name as the job. This was left behind from the rather sudden system shutdown.

The solution?

drop table sys.jobname

and everything returns to normal.

It is worth considering job names for datapump – make sure there isn’t already an object within the database with that name or you’ll hit the same problem.