ORA-31626, ORA-31633 while running Datapump job

Today I’m doing schema refresh from dev to test server in my LINUX environment, I placed all the parameters in a parfile(schema_expdp.par) and put that parfile in shell script(expdp_SYS.sh) and started running with NOHUP command.

$nohup sh expdp_SYS.sh >schema.out&

In the schema.out (log file for nohup job used for running expdp job) I found the below error:

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.FULL_EXPDP_DEVDB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object

After some research, I found the cause of this error as given below:

Cause: This error occurred because Job creation failed because a Master Table(A table created when a Datapump job is started and dropped when a Datapump job is completed) and its indexes could not be created,most commonly due to the pre-existence of a table with the same name (job name) in the user schema

Solution:  drop table sys.job_name and everything returns to normal or  change the datapump JOB_NAME.

I decided to Change the JOB_NAME in the expdp script and run the expdp script once again with the help of the 'nohup' command as given below:

$vi schema_expdp.par

DIRECTORY=DPUMP_DIR1

DUMPFILE=SYSFM_%U.dmp

LOGFILE=LOGFILE_DIR1:sysfm.log

PARALLEL=16

SCHEMAS=SYSFM

JOB_NAME=newsysfm_expdp                        (it was sysfm_expdp, now newsysfm_expdp)

:wq

$nohup sh expdp_sys.sh>schema.out&

This time the 'nohup' command ran successfully, I checked the 'expdp' job it started successfully and the log file also looks fine.

$ps -ef|grep expdpDatapump(expdp) job found running

Checked the logfile in the location specified and found successfully running

EXPLANATION :

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.job_name and everything returns to normal or change the JOB_NAME.

NOTE :  

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.