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.
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.