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_path\dpump_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.

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.

Answer 2

If you open the DMP file with an editor that can handle big files, you might be able to locate the areas where the schema names are mentioned. Just be sure not to change anything. It would be better if you opened a copy of the original dump.