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.

One Response to Datapump Failure

  1. Fahd Mirza says:

    And my data pump job failed last night due to ORA-04031. It had to do with the fragmented shared pool.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: