oracle 如何使用'imp'命令在Oracle中仅导入没有索引和约束的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1432478/
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 import just the data without indexes and constraints in Oracle using 'imp' command
提问by Sanjay Thakur
We get *.dmp files from client which has some masked table data including indexes and constraints. I do have those table structures (including indexes and constraints) at my end. I want to import just the data without the indexes and constraints (present in the .dmp file) in Oracle10g using 'imp' command.
我们从客户端获取 *.dmp 文件，其中包含一些掩码表数据，包括索引和约束。我确实有这些表结构（包括索引和约束）。我想在 Oracle10g 中使用“imp”命令仅导入没有索引和约束（存在于 .dmp 文件中）的数据。
I am aware of the 'imp' command. Do help me in letting me know the options available in 'imp' command to import only the data.
我知道“imp”命令。请帮助我让我知道“imp”命令中可用的选项来仅导入数据。
I have tried using -- rows=yes indexes=no but this does not help.
我曾尝试使用 --rows=yes index=no 但这无济于事。
回答by RC.
You should be able to specify indexes=N and constraints=N.
您应该能够指定索引=N 和约束=N。
For more info:
欲了解更多信息：
%> imp help=y
Here is a link with some good info on the options: Oracle imp information
这是一个包含一些关于选项的好信息的链接： Oracle imp information
回答by APC
I am assuming from your post that you already have the tables and ancillary structures in your database, and you just want to suppress the error messages. If that is indeed the case the option you want is IGNORE=Y.
我从您的帖子中假设您的数据库中已经有了表和辅助结构，而您只想抑制错误消息。如果确实如此，您想要的选项是IGNORE=Y.
The Oracle documentaion is available online. You don't say what version you're on, but as you're using IMP I'd say 9i was a good fit. Find out more.. (On later versions you should check out DataPump instead).
Oracle 文档可在线获取。您没有说明您使用的是哪个版本，但是当您使用 IMP 时，我会说 9i 很合适。 了解更多。. （在更高版本中，您应该改为查看 DataPump）。
回答by DBA
Import the dump with show=y option. This will create/extract the scripts from dmp file. Now you can remove the indexes and constraint scripts from the log and execute against the database.
使用 show=y 选项导入转储。这将从 dmp 文件中创建/提取脚本。现在您可以从日志中删除索引和约束脚本并针对数据库执行。
Here you can see lot of exp/data pump related examples. http://www.acehints.com/p/site-map.html
在这里你可以看到很多与 exp/data pump 相关的例子。 http://www.acehints.com/p/site-map.html
回答by Steef
You need to disable all triggers and then import your data with the CONSTRAINTS=N argument. Consider importing a table G3E_COMPONENT with constraints, foreign keys and triggers:
您需要禁用所有触发器，然后使用 CONSTRAINTS=N 参数导入数据。考虑导入带有约束、外键和触发器的表 G3E_COMPONENT：
SQL>alter table G3E_COMPONENT disable all triggers;
import your data:
导入您的数据：
imp userid=USER/XXXXX@ORCL CONSTRAINTS=N DATA_ONLY=Y STATISTICS=NONE file=export.exp log=imp.log TABLES=G3E_COMPONENT
Should do the trick
应该做的伎俩
回答by Juraj
IMHO IMP can't prevent constraints being applied and triggers being fired, ignore=y only ignores errors that arise. Maybe datapump allows it, I don't know.
恕我直言，IMP 无法阻止应用约束和触发触发器，ignore=y 只会忽略出现的错误。也许数据泵允许它，我不知道。
So you have to:
所以你必须：
- manually disable all triggers and constraints on imported table
- do an import with tables=<table names> rows=Y indexes=N constraints=N
- enable triggers
- enable validate constraints and resolve any errors (find and edit/remove offending values).
- 手动禁用导入表上的所有触发器和约束
- 使用表=<表名称> 行=Y 索引=N 约束=N 进行导入
- 启用触发器
- 启用验证约束并解决任何错误（查找和编辑/删除违规值）。
回答by pero
Be careful to use imp version that exactly matches your DB version. I had trouble with this...
小心使用与您的数据库版本完全匹配的 imp 版本。我遇到了这个问题...
回答by gaurav awasthi
Do Ignore=Y. It will ignore the create errors since you have already have the schema.
做Ignore=Y。它将忽略创建错误，因为您已经拥有架构。
