导入 Oracle 数据转储并覆盖现有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23144701/
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
Import Oracle data dump and overwrite existing data
提问by Stark
I have an oracle dmp file and I need to import data into a table. The data in the dump contains new rows and few updated rows. I am using import command and IGNORE=Y, so it imports all the new rows well. But it doesn't import/overwrite the existing rows (it shows a warning of unique key constraint violated). Is there some option to make the import UPDATE the existing rows with new data?
我有一个 oracle dmp 文件，我需要将数据导入表中。转储中的数据包含新行和少量更新行。我正在使用导入命令和 IGNORE=Y，因此它可以很好地导入所有新行。但它不会导入/覆盖现有行（它显示违反唯一键约束的警告）。是否有一些选项可以使导入用新数据更新现有行？
回答by Alex Poole
No. If you were using data pump then you could use the TABLE_EXISTS_ACTION=TRUNCATEoption to remove all existing rows and import everything from the dump file, but as you want to update existing rows and leave any rows not in the new file alone - i.e. not delete them (I think, since you only mention updating, though that isn't clear) - that might not be appropriate. And as your dump file is from the old exptool rather than expdpthat's moot anyway, unless you can re-export the data.
不。如果您使用的是数据泵，那么您可以使用该TABLE_EXISTS_ACTION=TRUNCATE选项删除所有现有行并从转储文件中导入所有内容，但因为您想更新现有行并将任何行单独保留在新文件中 - 即不删除它们（我认为，因为你只提到更新，虽然不清楚） - 这可能不合适。而且由于您的转储文件来自旧exp工具而不是expdp没有实际意义，除非您可以重新导出数据。
If you do want to delete existing rows that are not in the dump then you could truncate all the affected tables before importing. But that would be a separate step that you'd have to perform yourself, its not something impwill do for you; and the tables would be empty for a while, so you'd have to have downtime to do it.
如果确实要删除不在转储中的现有行，则可以在导入之前截断所有受影响的表。但这将是一个单独的步骤，您必须自己执行，它不会imp为您做任何事情；并且桌子会空一段时间，因此您必须有停机时间才能这样做。
Alternatively you could import into new staging tables - in a different schema sinceimpdoesn't support renaming either - and then use those to mergethe new data into the real tables. That may be the least disruptive approach. You'd still have to design and write all the mergestatements though. There's no built-in way to do this automatically.
或者，您可以导入新的临时表 - 在不同的模式中，因为imp也不支持重命名 - 然后将它们用于merge新数据到真实表中。这可能是破坏性最小的方法。不过，您仍然需要设计和编写所有merge语句。没有内置的方法可以自动执行此操作。
回答by olekb
You can import into temp table and then do record recon by joining with it.
您可以导入到临时表中，然后通过加入它来进行记录侦察。
Use impdp option REMAP_TABLE to load existing file into temp table.
使用 impdp 选项 REMAP_TABLE 将现有文件加载到临时表中。
impdp .... REMAP_TABLE=TMP_TABLE_NAME
when load is done run MERGE statement on existing table from temp table.
加载完成后，对临时表中的现有表运行 MERGE 语句。
