Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line:

impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp logfile=import.log

I get the errors:

ORA-31655: no data or metadata objects selected for job

ORA-39154: Objects from foreign schemas have been removed from import

And no data is imported.

From what I've Googled, one possible cause of this is that I need to specify remap_schama. But I have no idea what the name is of the schema in the dmp file. Any easy way to find out?

EDIT: I didn't find a solution to this question, but I did find a workaround... I tracked down the guy who made the DMP, and beat got the schema name out of him. Specified remap_schema according to his definition, and Hey Presto!

share|improve this question
    
Thanks for the suggestion: the beat workaround worked fine for me as well. bePpe –  user28452 Sep 19 '13 at 8:04

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
What is that first parameter `phil/phil'? What should I be replacing it with? –  Shaul Mar 13 '13 at 15:26
1  
It's the username and password.... –  Phil Mar 13 '13 at 15:28
1  
D'oh! ..... :-) –  Shaul Mar 13 '13 at 15:29
    
Hm, all I got was this: -- CONNECT PS ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; –  Shaul Mar 13 '13 at 15:32
    
Courtesy ping - I found the original schema name. So the question is kinda moot now... Thanks anyway for your help! –  Shaul Mar 14 '13 at 13:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.