Datapump Failure

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.