oracle 使用impdp导入选择性数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/686297/
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
Importing selective data using impdp
提问by atlantis
I have an entire DB to be imported as a dump into my own. I want to exclude data out of certain tables(mostly because they are huge in size and not useful). I cannot entirely exclude those tables since I need the table object per se(minus the data) and will have to re create them in my schema if I do so. Also in the absence of those table objects , various other foreign constraints defined on other tables will also fail to be imported and will need to be redefined.So I need to exclude just the data from certain tables.I want data from all other tables though.
我有一个完整的数据库要作为转储导入到我自己的数据库中。我想从某些表中排除数据（主要是因为它们的大小很大而且没有用）。我不能完全排除这些表，因为我需要表对象本身（减去数据）并且如果我这样做将不得不在我的模式中重新创建它们。同样在没有这些表对象的情况下，在其他表上定义的各种其他外部约束也将无法导入，需要重新定义。所以我只需要排除某些表中的数据。不过我想要所有其他表中的数据.
Is there a set of parameters for impdp that can help me do so?
是否有一组 impdp 参数可以帮助我这样做？
回答by DCookie
I would make two runs at it: The first I would import metadata only:
我会运行两次：第一次我只导入元数据：
impdp ... CONTENT=METADATA_ONLY
impdp ... CONTENT=METADATA_ONLY
The second would include the data only for the tables I was interested in:
第二个将仅包含我感兴趣的表的数据：
impdp ... CONTENT=DATA_ONLY TABLES=table1,table2...
impdp ... CONTENT=DATA_ONLY TABLES=table1,table2...
回答by MichaelN
Definitely make 2 runs. One to create all the table objects, but instead of using tables in the second impdp run, use the exclude
肯定跑2次。一个创建所有表对象，但不是在第二次 impdp 运行中使用表，而是使用 exclude
impdp ... Content=data_only exclude=TABLE:"IN ('table1', 'table2')"
The other way works, but this way you only have to list the tables you don't want versus all that you want.
另一种方式有效，但这种方式您只需列出您不想要的表与您想要的所有表。
回答by mmahroug
Syntax:
句法：
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
Examples of operator-usage:
运算符用法示例：
EXCLUDE=SEQUENCE
or EXCLUDE=TABLE:"IN ('EMP','DEPT')"
or EXCLUDE=INDEX:"= 'MY_INDX'"
or INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
or INCLUDE=TABLE:"> 'E'"
The parameter can also be stored in a parameter file, for example: exp.par
参数也可以存储在参数文件中，例如： exp.par
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"
回答by vikas pandey
If the size of the table is big for export import the you can use "SAMPLE" parameter in expdp command to take export of table for what ever percentage you want ....
如果表的大小对于导出导入来说很大，您可以在 expdp 命令中使用“SAMPLE”参数来导出您想要的任何百分比的表......
$ expdp tables=T100test DIRECTORY=expimp1 DUMPFILE=test12.dmp SAMPLE = 10;
This command will export only 10% data of the T100test table's data.
此命令将仅导出 T100test 表数据的 10% 的数据。
回答by k4cy
It seems you can exclude directly when importing using impdp query parameter
使用impdp查询参数导入时似乎可以直接排除
impdp [...] QUERY='TABLE_NAME:"WHERE rownum = 0"'
cf : community.oracle.com
