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 )

 

 2 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

IT Tutorial