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.

Spongioblast

Answer 1

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.

Campbell

Answer 2

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.

Coauthor

Curious why the downvote, I've literally used this method myself on several occasions to determine schemas, and it's a heck of a lot simpler than the other answers of "social engineering" or "dig around a dmp" – Coauthor

Who would downvote this? It saved me from trying to parse a huge dump. – Kobold

Maybe they got "ORA-39145: directory object parameter must be specified and non-null" – Humerus

Hope you don't mind my edits. I was wondering about that detail, so I just found the answer and edited it in. – Cangue

Maybe the downvote because someone missed that you have to backslash the \'/ as sysdba\' and you forgot to mention the directory parameter. – Aeolipile

If you have a large dmp file, it helps to filter the object types filter included in the ddl e.g., to just see the users append INCLUDE=USER to the cmd. – Vercelli

Answer 3

Update (2008-09-19 10:05) - Solution:

My Solution: Social engineering, I dug real hard and found someone who knew the schema name.
Technical Solution: Searching the .dmp file did yield the schema name.
Once I knew the schema name, I searched the dump file and learned where to find it.

Places the Schemas name were seen, in the .dmp file:

• <OWNER_NAME>SOURCE_SCHEMA</OWNER_NAME> This was seen before each table name/definition.

• SCHEMA_LIST 'SOURCE_SCHEMA' This was seen near the end of the .dmp.

Interestingly enough, around the SCHEMA_LIST 'SOURCE_SCHEMA' section, it also had the command line used to create the dump, directories used, par files used, windows version it was run on, and export session settings (language, date formats).

So, problem solved :)

Spongioblast

Answer 4

Assuming that you do not have the log file from the expdp job that generated the file in the first place, the easiest option would probably be to use the SQLFILE parameter to have impdp generate a file of DDL (based on a full import). Then you can grab the schema names from that file. Not ideal, of course, since impdp has to read the entire dump file to extract the DDL and then again to get to the schema you're interested in, and you have to do a bit of text file searching for the various CREATE USER statements, but it should be doable.

Dancette

I had attempted to use the SQLFILE parameter, but it failed with a single line in the .sql output file: "-- CONNECT MYSCHEMA". I think with a little more effort getting it working, this could have yielded the solution. – Spongioblast

Answer 5

The running the impdp command to produce an sqlfile, you will need to run it as a user which has the DATAPUMP_IMP_FULL_DATABASE role.

Or... run it as a low privileged user and use the MASTER_ONLY=YES option, then inspect the master table. e.g.

select value_t 
from SYS_IMPORT_TABLE_01 
where name = 'CLIENT_COMMAND' 
and process_order = -59;

col object_name for a30
col processing_status head STATUS for a6
col processing_state head STATE for a5
select distinct
  object_schema,
  object_name,
  object_type,
  object_tablespace,
  process_order,
  duplicate,
  processing_status,
  processing_state
from sys_import_table_01
where process_order > 0
and object_name is not null
order by object_schema, object_name
/

http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/oow2011_dp_mastering.pdf

Rhododendron

Answer 6

Step 1: Here is one simple example. You have to create a SQL file from the dump file using SQLFILE option.

Step 2: Grep for CREATE USER in the generated SQL file (here tables.sql)

Example here:

$ impdp directory=exp_dir dumpfile=exp_user1_all_tab.dmp  logfile=imp_exp_user1_tab sqlfile=tables.sql

Import: Release 11.2.0.3.0 - Production on Fri Apr 26 08:29:06 2013

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

Username: / as sysdba

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 08:29:12

$ grep "CREATE USER" tables.sql

CREATE USER "USER1" IDENTIFIED BY VALUES 'S:270D559F9B97C05EA50F78507CD6EAC6AD63969E5E;BBE7786A5F9103'

Lot of datapump options explained here http://www.acehints.com/p/site-map.html

Haigh

Answer 7

You need to search for OWNER_NAME.

cat -v dumpfile.dmp | grep -o '<OWNER_NAME>.*</OWNER_NAME>' | uniq -u

cat -v turn the dumpfile into visible text.

grep -o shows only the match so we don't see really long lines

uniq -u removes duplicate lines so you see less output.

This works pretty well, even on large dump files, and could be tweaked for usage in a script.

Thyratron

Answer 8

My solution (similar to KyleLanser's answer) (on a Unix box):

strings dumpfile.dmp | grep SCHEMA_LIST

Feldspar

Answer 9

In my case, based on Aldur's and slafs' answers I came up with this expression that should tell you just the name of the original schema:

cat -v file.dmp | grep 'SCHEMA_LIST' | uniq -u | grep -o -P '(?<=SCHEMAS\=).*(?=content)'

Tested for a DMP file from Oracle 19.8 version.

Couchman