ORA-31684: Object type already exists

I got “ORA-31684: Object type already exists ”  error in Oracle database.

 

ORA-31684: Object type already exists

 

Details of error are as follows.

DataPump import (IMPDP) fails with error ORA-31684, if the parameter EXCLUDE=USER:”=<USER_NAME>” is specified.

#> impdp / DUMPFILE=DB2P.DMP EXCLUDE=USER:\"=\'DBP2_TOO\'\" REMAP_SCHEMA=DB2P:DBP2_TOO DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_DBP2_TOO_3.LOG

Import: Release 11.2.0.1.0 - Production on Tue Apr 27 15:43:49 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "OPS$ORASBP01"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "OPS$ORASBP01"."SYS_IMPORT_FULL_01": /******** dumpfile=db2p.dmp exclude=USER:"='DBP2_TOO'" remap_schema=db2p:dbp2_too directory=data_pump_dir logfile=imp_dbp2_too_3.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"DBP2_TOO" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

 

 

Object type already exists

This ORA-31684 errors are related with the Object type already exists.

 

The following parameters have been specified:

 REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>
 EXCLUDE=USER:"='<TARGET_SCHEMA>'"

Performing a schema export from the SOURCE_SCHEMA schema, all the metadata in the export dump file point to the SOURCE_SCHEMA schema.

 

With exclude=USER:”='<USER_NAME>'” , IMPDP is excluding the DDL, that it is in the export dump file, to create the USER_NAME schema.

 

With REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>, the IMPDP after reading the metadata from export dump file will replace the SOURCE_SCHEMA string with TARGET_SCHEMA.

 

The EXCLUDE parameter is applied before remapping the schema (REMAP_SCHEMA parameter) so EXCLUDE=USER:”='<TARGET_SCHEMA>'” doesn’t exclude the CREATE USER <SOURCE_SCHEMA> DDL.

 

To solve this error, Specify the SOURCE_SCHEMA in the EXCLUDE=USER parameter:

 EXCLUDE=USER:"='<SOURCE_SCHEMA>'"


#> impdp / DUMPFILE=DB2P.DMP EXCLUDE=USER:\"=\'DB2P\'\" REMAP_SCHEMA=DB2P:DBP2_TOO DIRECTORY=DATA_PUMP_DIR

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )