Oracle imp dmp 文件，跳过创建表语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3392932/
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
Oracle imp dmp file, create table statements skipped
提问by actionscript3
I get a dmp and install a Oracle XE for developing. However, when I use imp to import, the create table statements were skipped. Can anyone kindly help on the issue.
我得到了一个 dmp 并安装了一个用于开发的 Oracle XE。但是，当我使用 imp 导入时，跳过了创建表语句。任何人都可以帮助解决这个问题。
The screen output were captured as follow: Many Thanks!
屏幕输出捕获如下： 非常感谢！
C:\Documents and Settings\wong\Desktop>imp
Import: Release 10.2.0.1.0 - Production on Mon Aug 2 22:48:32 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: report
Password:
密码：
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio n
Import file: EXPDAT.DMP > report20100524.dmp
Enter insert buffer size (minimum is 8192) 30720> 30720
Export file created by EXPORT:V10.01.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) List contents of import file only (yes/no): no > yes
Import entire export file (yes/no): no
> yes
. importing REPORT's objects into REPORT "BEGIN " "sys.dbms_logrep_imp.instantiate_schema(schema_name=>'REPORT', export_db_na" "me=>'SPACESAT', inst_scn=>'650909');" "COMMIT; END;" "CREATE TABLE "BASE_COUNTRY" ("IN_CODE" VARCHAR2(60) NOT NULL ENABLE, "OUTCO" "DE" VARCHAR2(60), "OUTNAME" VARCHAR2(60), "LEVELID" NUMBER(1, 0), "IS_LEAF"" " NUMBER(1, 0), "SX" VARCHAR2(10), "PHONE" VARCHAR2(20), "TIMEX" NUMBER(2, 0" ")) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FRE" "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING " "NOCOMPRESS" . . skipping table "BASE_COUNTRY"
.... many tables skipped...
"CREATE UNIQUE INDEX "XF_TC_FLOOR_U1" ON "XF_TC_FLOOR" ("XF_FLOOR" , "XF_GSI" "D" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 " "FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "CREATE TRIGGER "YREPORT".xf_tc_floor_insupd BEFORE INSERT OR UPDATE" " ON xf_tc_floor" " FOR EACH ROW" " BEGIN" " :new.xf_lastmodtime := sysdate;" " :new.xf_lastuploadtime := sysdate;" " END xf_tc_floor_insupd" ";" "ALTER TRIGGER "XF_TC_FLOOR_INSUPD" ENABLE" mport terminated successfully without warnings.
C:\Documents and Settings\wong\Desktop>
回答by Gary Myers
The IMP you are using is only importing objects owned by REPORT. Tables/indexes etc owned by other schemas won't be imported (mostly because there is a good chance that REPORT won't be able to create table in those schemas).
您使用的 IMP 仅导入 REPORT 拥有的对象。其他模式拥有的表/索引等将不会被导入（主要是因为 REPORT 很有可能无法在这些模式中创建表）。
You could look into the FROMUSER/TOUSER command line parameters, but you may also need to use a privileged user for the import.
您可以查看 FROMUSER/TOUSER 命令行参数，但您可能还需要使用特权用户进行导入。
回答by James Anderson
This could be due to the restrictions of Oracle XE(Express i.e. the freebie version). Specifically only one predefined "user" tablespace and a 4GB limit on storage.
这可能是由于 Oracle XE（Express 即免费版）的限制。特别是只有一个预定义的“用户”表空间和 4GB 的存储限制。
While this doesnt seem too restrictive the tablespace and "initial" storage sizes are embedded in the export files table definitions. If your the exporting system specified a tablespace other than users or specified an "initial" storage allocation that cumulativly exceeds your 4GB storage allocation the import will fail. So unless the export file was created by another XE database you are likely to have problems.
虽然这似乎并不太严格，但表空间和“初始”存储大小嵌入在导出文件表定义中。如果您的导出系统指定了用户以外的表空间，或者指定了累积超过 4GB 存储分配的“初始”存储分配，则导入将失败。因此，除非导出文件是由另一个 XE 数据库创建的，否则您可能会遇到问题。
You can capture the DDL (sort off) by using the -show option of the imp command.
您可以使用imp 命令的-show 选项捕获DDL（排序）。
You can also edit the .imp file to remove the "tablespace" and "initial" clauses from the DDL -- but make sure you backup the original file first as you can easily make the file unreadable.
您还可以编辑 .imp 文件以从 DDL 中删除“表空间”和“初始”子句——但请确保先备份原始文件，因为您很容易使文件不可读。
