Problem solving with DBMS_DATAPUMP

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?