Avoid already existing objects when importing into Oracle

impdporacleoracle-11g-r2

I need help with impdp. I have taken a backup with:

expdp system/pw@orcl full=y directory=abc dumpfile=full.dmp

Now I need to import the dump into another instance, which has recently been created. I was going to import the full database dump, but it raises an error when an object already exists.

However, these objects belong to Oracle's default schemas, which I can not remove. How do I avoid these import errors?

Best Answer

Exclude the schemas with the exclude parameter. E.g:

EXCLUDE=SCHEMA:\"IN\(\'SYS\',\'SYSTEM\',\'OUTLN\',\'ORDDATA\',\'OLAPSYS\',\'MDDATA\',\'SPATIAL_WFS_ADMIN_USR\',\'ANONYMOUS\',\'SPATIAL_CSW_ADMIN_USR\',\'ANONYMOUS\',\'FLOWS_FILES\',\'APEX_PUBLIC_USER\',\'APEX_030200\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'SCOTT\',\'SYSMAN\',\'MGMT_VIEW\'\)\"

The list of schemas depends on the database version and options installed.

In a parameterfile, you can use the above without \ (escaping special characters).

Related Solutions

How to find the schema name of a data pump dmp file

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.

Trigger needs dbms_alert declared

I think you may be missing synonym for dbms_alert for adminuser schema. A object grant and synonym both are normally required (unless you own the object).

Try following as sys account, perhaps it will fix the problem.

create or replace synonym adminuser.dbms_alert for sys.dbms_alert;

If you don't want to create a synonym, you should use sys.dbms_alert, if your create a synonym as shown above, using just dbms_alert is sufficient in your code.

Best Answer

Related Solutions

How to find the schema name of a data pump dmp file

Trigger needs dbms_alert declared

Related Question