PARTITION | PARTITIONEXCLUDE

Valid For

Extract, Distribution Service, and Replicat. Oracle only.

Description

These parameters work in conjunction with the TABLE and TABLEEXCLUDE parameters. Only when a table is included, the partition rules are evaluated.

For consistency, these parameters behave much like their TABLE and TABLEEXCLUDE counterparts.

• Wildcarding will be allowed in all name portions.

• GLOBALS parameter EXCLUDEWILDCARDOBJECTSONLY is supported

• Container portion is only valid in CDB environment in the three-part name.

• SOURCECATALOG parameter will take affect when catalog portion is not specified.

• Container portion must be specified when SOURCECATALOG is not specified.

• If container portion is specified, then it takes precedence over SOURCECATALOG.

If the [container.]schema.table portion of any PARTITION or PARTITIONEXCLUDE rule matches the table, only then additional partition filtering will be performed.

Partition filtering rules are evaluated in the following order:

• If the partition name does not match any PARTITION parameter, it is excluded.

• If included by the PARTITION parameter, then exclusion rules are evaluated unless it was included by a non-wildcard inclusion rule and EXCLUDEWILDCARDOBJECTSONLY was specified.

If using PARTITION or EXCLUDEPARTITION in Replicat, then the PARTITION parameter must be used for the Extract TABLE parameter to write the partition metadata into the trail file so that Replicat can process it.

Note:

An error occurs if a PARTITION or PARTITIONEXCLUDE parameter has an invalid number of parts.

Syntax

PARTITION [container.]schema.table.partition;
PARTITIONEXCLUDE [container.]schema.table.partition;

For non-CDB, 3 parts must be specified (schema.table.partition).

For CDB, either 4 parts must be specified (pdb.schema.table.partition) or 3 parts with a preceding SOURCECATALOG parameter.

Examples

In the following example with DML operations on partition P_Q4 of table SH.SALES, the partition is included because both table and partition rules include it.

TABLE
  sh.sales;
  PARTITION sh.sales*.p_q4;

In the following example with DML operations on partition P_Q4 of table SH.SALES, all partitions are included on the Extract side using the TABLE/PARTITION parameters. The partition P_Q4 is excluded at the Replicat side using the MAP/PARTITIONEXCLUDE parameters. All other changes on partitions are applied by Replicat.

TABLE sh.sales;
  PARTITION sh.sales.p_q*;

MAP sh.sales, TARGET sh.sales;
  PARTITION sh.sales.p_q4;

In the following example with DML operations on partition P_Q4 of table SH.SALES, the partition P_Q4 is excluded because it is only valid for the partition P_Q3 of SH.SALES:

TABLE
   sh.sales;PARTITION
   sh.sales*.p_q3;

The following example with DML operations on partition P_Q3 of SH.SALES shows how multiple partition rules can be specified. Partition will be included because it is matched by one of the partition inclusion rules.

TABLE
          sh.sales;PARTITION
          sh.sales.p_q3;PARTITION
          sh.sales.p_q4;

In the following example with DML operations on partition P_Q4 of table SH.SALES_HISTORY, the partition P_Q4 is excluded because of explicit partition exclude rule.

TABLE
  sh.sales_history
  PARTITION sh.sales*.p_q4;
  PARTITIONEXCLUDE sh.sales_history.p_q*;

In the following example with DML operations on partition P_S1 of table SH.PRODUCTS, neither the partition PART_S1 nor the complete table SH.PRODUCTS is captured because the table SH.PRODUCTS is not referenced.

TABLE
  sh.sales;PARTITION
  sh.products.p_s1;

In the following example with DML operations on partition P_Q4 of table SH.SALES, the partition PART_S1 is included because no partition rule has a table portion matching SH.SALES. Therefore, no partition rule is evaluated.

TABLE
  sh.sales;
  PARTITION sh.products.p_s1;