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 gt  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

User · Answer

You need to search for OWNER NAME     cat -v dumpfile dmp   grep -o   lt OWNER NAME gt    lt  OWNER NAME gt     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

User · Answer

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

User · Answer

My solution  similar to KyleLanser s answer   on a Unix box    strings dumpfile dmp   grep SCHEMA LIST

User · Answer

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  gt  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

User · Answer

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

User · Answer

My solution  similar to KyleLanser s answer   on a Unix box    strings dumpfile dmp   grep SCHEMA LIST

User · Answer

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  lt your  dmp file gt  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

User · Answer

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

User · Answer

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

User · Answer

You need to search for OWNER NAME     cat -v dumpfile dmp   grep -o   lt OWNER NAME gt    lt  OWNER NAME gt     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

User · Answer

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     lt OWNER NAME gt SOURCE SCHEMA lt  OWNER NAME gt  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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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     lt OWNER NAME gt SOURCE SCHEMA lt  OWNER NAME gt  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

User · Answer

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  lt your  dmp file gt  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

User · Answer

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

[oracle] How to determine the Schemas inside an Oracle Data Pump Export file

Examples related to oracle

Examples related to import

Examples related to oracle-dump