oracle 列出Oracle导出转储文件的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8179859/
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
List the contents of Oracle export dump file
提问by Kerby82
I have an oracle full dump. I know I have to imp the dump with the fromusertouserclause. However I don't know the names of the schemas included in the dump.
我有一个 oracle full dump。我知道我必须使用该fromusertouser条款进行转储。但是我不知道转储中包含的模式的名称。
how can I open the dump created with Oracle 10g just to check and analyze the content?
如何打开用 Oracle 10g 创建的转储来检查和分析内容？
回答by Sathyajith Bhat
Just use the SHOW=Yparameter
只需使用SHOW=Y参数
IMP SCOTT/TIGER SHOW=Y FILE=import_file.dmp
回答by shinobi92
imp SCOTT/tiger show=Y fiLE=IMPORT_FILE.DMP log=**logfile.log** fromuser=scott touser=scott
if you are only interested in the name of object names and schema this is good for list of objects not the contents inside table.
如果您只对对象名称和模式的名称感兴趣，这对于对象列表而不是表中的内容很有用。
once your command is successfully finished, use your favorite text editor like notepad /vim to open the log file generated by imp command by adding parameter LOG=logfile.log. in this file imp command will output all the actions it will perform as real case scenario. now search for the text "importing" on each location you will get SCHEMA NAMES followed by the object_names contained in that schema.
一旦你的命令成功完成，使用你喜欢的文本编辑器，比如记事本/vim，通过添加参数LOG=logfile.log打开imp命令生成的日志文件。在这个文件中，imp 命令将输出它将作为真实情况执行的所有操作。现在在每个位置搜索文本“ importing”，您将获得 SCHEMA NAMES 后跟包含在该架构中的 object_names。
followed by commands to create/alter the objects as they were in original database.
after that the data insertions will be simulated you may get idea how many records in each schema.
后跟创建/更改对象的命令，就像它们在原始数据库中一样。
之后将模拟数据插入，您可能会知道每个模式中有多少条记录。
but it won't list them.
但它不会列出它们。
SAMPLE import file for importing just one table EMP
SAMPLE 导入文件，只导入一张表 EMP
---------start output of imp command --------
---------开始imp命令的输出--------
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
**. importing SCOTT's objects into SCOTT**
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
"BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
"LT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "EMP"
"CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX"
"TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D"
"EFAULT) TABLESPACE "USERS" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"
"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE"
"LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE "
"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully without warnings.
---------end of out of imp command ---------
---------输出imp命令结束---------
This tool claims and offers you to take a dump file and put the data into Excel/Access/CSV formated file. which maybe what you are looking for.
该工具声称并提供您获取转储文件并将数据放入 Excel/Access/CSV 格式的文件中。这也许是您正在寻找的。
it show whats inside the tables with out import to real database
它显示表中的内容，无需导入真实数据库
just in case someone is looking to find which dump file contains the data needed.
以防万一有人要查找哪个转储文件包含所需的数据。
instead of going through all the old dumps you should keep a logfile with same name of each DUMPFILE suffix appended with its date and time.
like FULL_DB_DUMP_15012014_1240pm.DMP
您应该保留一个日志文件，每个 DUMPFILE 后缀的名称都相同，并附加其日期和时间，而不是遍历所有旧的转储。
像FULL_DB_DUMP_15012014_1240pm.DMP
so that you just open the relevant log file to get the idea which files contain what schema of your need or interest.
这样您只需打开相关的日志文件即可了解哪些文件包含您需要或感兴趣的架构。
[2]: ON a second thought: if your dump file is upto 2GB and you can identify ORACLE SQL.
Use a hex editor like HxD, Hexor, HexEdit in readonly mode to open the dump file. Once opened you can browse the contents of dump file in binary/ascii form. this may be a crude method but it will give you a partial view of schema and objects names DDL with some contents of views/triggers/Functions/Procedures etc. Although, you will not be able to read & understand the whole contents. but it servers the purpose. maybe relatively fast.
[2]：再想一想：如果您的转储文件高达 2GB 并且您可以识别 ORACLE SQL。
在只读模式下使用 HxD、Hexor、HexEdit 等十六进制编辑器打开转储文件。打开后，您可以以二进制/ASCII 格式浏览转储文件的内容。这可能是一个粗略的方法，但它会给你模式和对象名称 DDL 的部分视图，以及视图/触发器/函数/过程等的一些内容。虽然，你将无法阅读和理解整个内容。但它服务于目的。也许比较快。
回答by stw2019
if you are working in a UNIX environment, you may use command strings to show readable information from the dump files.
如果您在 UNIX 环境中工作，您可以使用命令字符串来显示转储文件中的可读信息。
strings test.dmp|grep CREATE.*TABLE
字符串 test.dmp|grep CREATE.*TABLE
