oracle 在数据泵导出期间排除特定的表分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11761262/
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
Excluding a particular table partition during data pump export
提问by Raihan
The exclude parameter can be used to filter out schema objects during export in Oracle Data Pump.
exclude 参数可用于在 Oracle 数据泵中导出期间过滤掉模式对象。
EXCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]
Is table partitiona valid object_type?
是table partition有效的object_type吗？
In other words, is it posible to exclude selected table partitions during export?
换句话说，是否可以在导出期间排除选定的表分区？
采纳答案by Raihan
It seems the only way to include/exclude selected partitions during export is to use the datapump API. Following is an eample:
在导出期间包含/排除选定分区的唯一方法似乎是使用数据泵 API。下面是一个例子：
http://www.acehints.com/2011/06/data-pump-expdp-how-to-exclude-table.html
http://www.acehints.com/2011/06/data-pump-expdp-how-to-exclude-table.html
回答by Alex Poole
The section of the documentation that covers filtering during export operationssays:
文档中涵盖导出操作期间过滤的部分说：
Metadata filtering is implemented through the EXCLUDE and INCLUDE parameters...
Metadata filters identify a set of objects to be included or excluded from an Export or Import operation...
To see a list of valid object types, query the following views: DATABASE_EXPORT_OBJECTS for full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode. The values listed in the OBJECT_PATH column are the valid object types.
元数据过滤是通过 EXCLUDE 和 INCLUDE 参数实现的...
元数据过滤器识别一组要从导出或导入操作中包含或排除的对象...
要查看有效对象类型的列表，请查询以下视图：完整模式的 DATABASE_EXPORT_OBJECTS、模式模式的 SCHEMA_EXPORT_OBJECTS 和表和表空间模式的 TABLE_EXPORT_OBJECTS。OBJECT_PATH 列中列出的值是有效的对象类型。
The first two views on my 11gR2 (EE) instance do not have any references to partitions; the third has some that refer to DBMS_PLUGTS, which doesn't appear in the PL/SQL Packages and Types Referencesection, but seems to be for transportable tablespaces.
我的 11gR2 (EE) 实例上的前两个视图没有任何对分区的引用；第三个有一些引用DBMS_PLUGTS，它没有出现在PL/SQL 包和类型参考部分，但似乎是用于可传输的表空间。
Not definitive, but based on that I'd have to say no, at least as separate object type.
不是确定的，但基于此我不得不说不，至少作为单独的对象类型。
So how about using the table:partitionsyntax that's valid in the TABLESclause? This doesn't work; if you try to include the partition name in the EXCLUDEclause:
那么如何使用子句中table:partition有效的语法TABLES呢？这不起作用；如果您尝试在EXCLUDE子句中包含分区名称：
expdp tables=MY_TABLE exclude=table:"= 'MY_TABLE:SOME_PARTITION'" ...
... it's ignored and the whole table is still exported - it seems to be treating the :as part of the table name, which isn't entirely unreasonable as it's in quotes, and therefore doesn't match against the table you're exporting at all. Same if you specify a schema to export, rather than just that table.
...它被忽略，整个表仍然被导出 - 它似乎将:视为表名的一部分，这并非完全不合理，因为它在引号中，因此与您正在导出的表不匹配根本。如果您指定要导出的架构，而不仅仅是该表，则相同。
The only option you seem to have is to specify the partitions you dowant in the TABLESclause.
你似乎有唯一的选择是指定分区做想要的TABLES条款。
回答by Tommy Petersen
You cannot exclude a table partition, using the TABLE key-word, what you want to do is to exclude the TABLE_DATA.
您不能使用 TABLE 关键字排除表分区，您要做的是排除TABLE_DATA.
exclude=table_data:"in ('SOME_PARTITION','SOME_OTHER_PARTITION')"
The manuals actually explains that.
手册实际上解释了这一点。
Unfortuanately any table_data matching will be excluded. This is really great if you partition your tables by period as in:
TRANSACTION.PD2010
TRANSACTION.PD2011
TRANSACTION.PD2012
TRANSACTION.PD2013
TRANSACTION_DETAIL.PD2010
TRANSACTION_DETAIL.PD2011
TRANSACTION_DETAIL.PD2012
TRANSACTION_DETAIL.PD2013
The partition names should not be the same as any table names, but you did of course take care of that when you planned the partitioning.
You can now do the exclude as follows:
不幸的是，任何 table_data 匹配都将被排除。如果您按时间段对表进行分区，这真的很棒：
TRANSACTION.PD2010
TRANSACTION.PD2011
TRANSACTION.PD2012
TRANSACTION.PD2013
TRANSACTION_DETAIL.PD2010
TRANSACTION_DETAIL.PD2011
TRANSACTION_DETAIL.PD2013
任何分区都不应与
TRANSACTION_DETAIL.PD2013
表名称相同，但是您在计划分区时当然会注意这一点。
您现在可以按如下方式进行排除：
exclude=table_data:"in (select partition_name from user_partitions where partition_name like 'PD%' and partition_name < 'PD' || to_char(sysdate,'YYYY'))"
You can also do the include the same way, using a select statement instead of a list of tables, that way you can get around the 4000 characters line limit. That can also be circumvented by multiple include statements, or by adding line breaks to the list of tables, however I do believe that if you have an in list, there can be only 1000 objects in that list, you can also do a table export, which exports only the tables, not the modules, triggers etc. That list is not limited to 1000 and again you can get around the 4000 character limit by adding line breaks.
您也可以使用相同的方式执行包含，使用 select 语句而不是表列表，这样您就可以绕过 4000 个字符的行限制。这也可以通过多个包含语句或通过在表列表中添加换行符来规避，但是我确实相信如果您有一个 in 列表，那么该列表中只能有 1000 个对象，您也可以执行表导出，它仅导出表，而不是模块、触发器等。该列表不限于 1000，您可以通过添加换行符再次绕过 4000 个字符的限制。
