Monday, August 17, 2009

ORA-31633: unable to create master table ( expdp )

ORA-31633: unable to create master table ( expdp )

Example

K:\Partion\expdmp>expdp USER_PROD/USER_PROD@PROD_DB parfile=ExportParam.txt
 
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 12 August, 2009 14:09:57
 
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, Real Application Clusters, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCHEMA_PROD.TABLE1"
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


Details


When performing and export using data pump you receive the error as above due to the
fact that the exp was previously executed and not completed due to the fact the it was
canceled or stopped for some reason. As a result the new expdp job has the same name
as the old expdp job.

Solution

Verify that the table associated with the expdp job exists:

SQL> select table_name from dba_tables where table_name like '%TABLE1%';
 
TABLE_NAME                                                                     
------------------------------                                                 
TABLE1 



Identified that data pump job exists and the status of the job is NOT RUNNING

SQL>SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;


Drop the table                                                          

SQL> drop table SCHEMA_PROD.TABLE1;
 
Table dropped.
 

Re Run the export



2 comments:

FRIENDSHIP said...

Thanks a lot for the info.

Maciej Leszczyński said...

Also create any table is needed if you are performing import from non sys schema with remapping schema names.