import - How to determine the Schemas inside an Oracle Data Pump Export file

Question

• I have an Oracle database backup file (.dmp) that was created with expdp.
• The .dmp file was an export of an entire database.
• I need to restore 1 of the schemas from within this dump file.
• I don't know the names of the schemas inside this dump file.
• To use impdp to import the data I need the name of the schema to load.

So, I need to inspect the .dmp file and list all of the schemas in it, how do I do that?

---

Update (2008-09-18 13:02) - More detailed information:

The impdp command i'm current using is:

impdp user/password@database directory=DPUMP_DIR 
      dumpfile=EXPORT.DMP logfile=IMPORT.LOG  

And the DPUMP_DIR is correctly configured.

SQL> SELECT directory_path
2  FROM dba_directories
3  WHERE directory_name = 'DPUMP_DIR';

DIRECTORY_PATH
-------------------------
D:directory_pathdpump_dir

And yes, the EXPORT.DMP file is in fact in that folder.

The error message I get when I run the impdp command is:

Connected to: Oracle Database 10g Enterprise Edition ...
ORA-31655: no data or metadata objects selected for the job
ORA-39154: Objects from foreign schemas have been removed from import

This error message is mostly expected. I need the impdp command be:

impdp user/password@database directory=DPUMP_DIR dumpfile=EXPORT.DMP 
      SCHEMAS=SOURCE_SCHEMA REMAP_SCHEMA=SOURCE_SCHEMA:MY_SCHEMA

But to do that, I need the source schema.

question from:https://stackoverflow.com/questions/95578/how-to-determine-the-schemas-inside-an-oracle-data-pump-export-file

Answer 1

impdp exports the DDL of a dmp backup to a file if you use the SQLFILE parameter. For example, put this into a text file

impdp '/ as sysdba' dumpfile=<your .dmp file> logfile=import_log.txt sqlfile=ddl_dump.txt

Then check ddl_dump.txt for the tablespaces, users, and schemas in the backup.

According to the documentation, this does not actually modify the database:

The SQL is not actually executed, and the target system remains unchanged.