如何确定 Oracle 数据泵导出文件中的架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/95578/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine the Schemas inside an Oracle Data Pump Export file
提问by KyleLanser
- I have a 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 dont 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.
- 我有一个用 expdp 创建的 Oracle 数据库备份文件 (.dmp)。
- .dmp 文件是整个数据库的导出。
- 我需要从这个转储文件中恢复 1 个模式。
- 我不知道这个转储文件中模式的名称。
- 要使用 impdp 导入数据，我需要加载架构的名称。
So, I need to inspect the .dmp file and list all of the schemas in it, how do i do that?
所以，我需要检查 .dmp 文件并列出其中的所有模式，我该怎么做？
Update (2008-09-18 13:02) - More detailed information:
更新 (2008-09-18 13:02) - 更详细的信息：
The impdp command i'm current using is:
我目前使用的 impdp 命令是：
impdp user/password@database directory=DPUMP_DIR
dumpfile=EXPORT.DMP logfile=IMPORT.LOG
And the DPUMP_DIR is correctly configured.
并且 DPUMP_DIR 配置正确。
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 infact in that folder.
是的，EXPORT.DMP 文件实际上就在该文件夹中。
The error message I get when I run the impdp command is:
运行impdp命令时收到的错误消息是：
Connected to: Oracle Database 10g Enterprise Edition ...
ORA-31655: no data or metadata objects selected for 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命令是：
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.
但要做到这一点，我需要源模式。
采纳答案by Petros
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.
如果您使用可以处理大文件的编辑器打开 DMP 文件，您可能能够找到提及架构名称的区域。只要确保不要改变任何东西。如果您打开原始转储的副本会更好。
回答by Factor Mystic
impdpexports the DDL of a dmpbackup to a file if you use the SQLFILEparameter. For example, put this into a text file
impdpdmp如果使用SQLFILE参数，则将备份的 DDL 导出到文件。例如，把它放到一个文本文件中
impdp '/ as sysdba' dumpfile=<your .dmp file> logfile=import_log.txt sqlfile=ddl_dump.txt
Then check ddl_dump.txtfor the tablespaces, users, and schemas in the backup.
然后检查ddl_dump.txt备份中的表空间、用户和模式。
According to the documentation, this does not actually modify the database:
根据文档，这实际上并没有修改数据库：
The SQL is not actually executed, and the target system remains unchanged.
SQL 并未实际执行，目标系统保持不变。
回答by KyleLanser
Update (2008-09-19 10:05) - Solution:
更新 (2008-09-19 10:05) - 解决方案：
My Solution:Social engineering, I dug real hard and found someone who knew the schema name.
Technical Solution:Searching the .dmp file didyield the schema name.
Once I knew the schema name, I searched the dump file and learned where to find it.
我的解决方案：社会工程学，我努力挖掘并找到了一个知道模式名称的人。
技术解决方案：搜索 .dmp 文件确实产生了架构名称。
知道模式名称后，我搜索转储文件并了解在哪里可以找到它。
Places the Schemas name were seen, in the .dmp file:
在 .dmp 文件中看到 Schemas 名称的位置：
<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.
<OWNER_NAME>SOURCE_SCHEMA</OWNER_NAME>这是在每个表名/定义之前看到的。SCHEMA_LIST 'SOURCE_SCHEMA'这在 .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).
有趣的是，围绕该SCHEMA_LIST 'SOURCE_SCHEMA'部分，它还具有用于创建转储的命令行、使用的目录、使用的 par 文件、运行它的 Windows 版本以及导出会话设置（语言、日期格式）。
So, problem solved :)
所以，问题解决了:)
回答by Justin Cave
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 parameterto 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.
假设您没有首先生成文件的 expdp 作业的日志文件，最简单的选择可能是使用SQLFILE 参数让 impdp 生成 DDL 文件（基于完全导入）。然后您可以从该文件中获取架构名称。当然，这并不理想，因为 impdp 必须读取整个转储文件以提取 DDL，然后再次访问您感兴趣的模式，并且您必须执行一些文本文件搜索来搜索各种 CREATE USER 语句，但应该是可行的。
回答by Peter Wiseman
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.
运行 impdp 命令以生成 sqlfile，您需要以具有 DATAPUMP_IMP_FULL_DATABASE 角色的用户身份运行它。
Or... run it as a low privileged user and use the MASTER_ONLY=YES option, then inspect the master table. e.g.
或者...以低权限用户身份运行它并使用 MASTER_ONLY=YES 选项，然后检查主表。例如
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
/
回答by DBA
Step 1: Here is one simple example. You have to create a SQL file from the dump file using SQLFILEoption.
第 1 步：这是一个简单的例子。您必须使用SQLFILE选项从转储文件创建一个 SQL 文件。
Step 2: Grep for CREATE USERin the generated SQL file (here tables.sql)
第 2 步：CREATE USER在生成的 SQL 文件中使用Grep for （这里是 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
导入：发布 11.2.0.3.0 - 2013 年 4 月 26 日星期五 08:29:06 生产
版权所有 (c) 1982、2011，Oracle 和/或其附属公司。版权所有。
用户名：/ as sysdba
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 作业 "SYS"."SYS_SQL_FILE_FULL_01" 在 08:29:12 成功完成
$ grep "CREATE USER" tables.sql
CREATE USER "USER1" IDENTIFIED BY VALUES 'S:270D559F9B97C05EA50F78507CD6EAC6AD63969E5E;BBE7786A5F9103'
创建按值标识的用户“USER1”'S:270D559F9B97C05EA50F78507CD6EAC6AD63969E5E;BBE7786A5F9103'
Lot of datapump options explained here http://www.acehints.com/p/site-map.html
这里解释了很多数据泵选项http://www.acehints.com/p/site-map.html
回答by Aldur
You need to search for OWNER_NAME.
您需要搜索 OWNER_NAME。
cat -v dumpfile.dmp | grep -o '<OWNER_NAME>.*</OWNER_NAME>' | uniq -u
cat -v turn the dumpfile into visible text.
cat -v 将转储文件转换为可见文本。
grep -o shows only the match so we don't see really long lines
grep -o 只显示匹配，所以我们看不到很长的行
uniq -u removes duplicate lines so you see less output.
uniq -u 删除重复的行，因此您看到的输出更少。
This works pretty well, even on large dump files, and could be tweaked for usage in a script.
这非常有效，即使在大型转储文件上也是如此，并且可以调整以在脚本中使用。
回答by slafs
My solution (similar to KyleLanser's answer) (on a Unix box):
我的解决方案（类似于 KyleLanser 的回答）（在 Unix 机器上）：
strings dumpfile.dmp | grep SCHEMA_LIST
