Oracle impdp error when importing data

datapumporacleoracle-12c

I get this error ORA-02289: sequence does not exist when I try to import a schema to my database, could you give me some recommendation?

Import Command:

impdp '"sys/mmm as sysdba"' directory=BACK_IMPORT dumpfile=0502190800%U.dmp 
logfile=impdp_MBD.log SCHEMAS=MSCHEM PARALLEL=7 encryption_password=0qazw

Export Command:

expdp '"sys/mmm as sysdba"' encryption=all encryption_password=0qazw 
encryption_algorithm=aes256 COMPRESSION=ALL DIRECTORY=BACK_EXPORT 
COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL flashback_time=systimestamp 
DUMPFILE=$name% U.dmp LOGFILE=$name.log PARALLEL=7 SCHEMAS=MSCHEM,IOOUT,ZHIST;

Best Answer

You are importing MSCHEM schema. Please check in source database that one of TABLE/Procedure/Package using a sequence that exist in another schema. If so, don't worry just recreate it. you can find this sequence name in import log also.

Related Solutions

Oracle 11g .DMP viewer

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR

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.

Best Answer

Related Solutions

Oracle 11g .DMP viewer

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

Related Question