SharePlex 9.2.7 - Installation and Setup for Oracle Source

You can use wildcard characters to specify multiple objects of a schema in one entry of the configuration file. SharePlex replicates any objects that satisfy the wildcard, except those that you explicitly exclude.

Note: Only object names can be wildcarded. Owner names cannot be wildcarded.

Requirements and limitations of wildcard support

The schemas that contain wildcarded object names must exist on the source and target before the configuration is activated.
(Oracle) The objects themselves do not have to exist before the configuration is activated, provided the correct parameters are set to required values. For more information, see Control Oracle DDL replication.
Wildcards are not allowed anywhere in a configuration entry that includes the following:
- Vertically partitioned replication. For more information, see Configure vertically partitioned replication.
- Horizontally partitioned replication. For more information, see Configure horizontally partitioned replication.
- (Oracle) Key definition. For more information, see Define a unique key.
- (Oracle) Column mapping. For more information, see Map source and target columns.
The tables that use these features must be specified in the configuration file separately.

Supported wildcard syntax

SharePlex supports the following SQL wildcards

Percent (%) wildcard to specify a string. (See the Examples.)
Underscore (_) wildcard to specify a single-character.
For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.

Specify wildcarded names in the configuration file

Use this template for help when specifying a wildcarded name in the configuration file.

Configuration with wildcarded object names

datasource_specification
expand src_owner.wildcard_name [not (list)]	tgt_owner.wildcard_name	routing_map

Description of syntax elements

Component	Description
expand	Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all objects that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed. Note: Leave a space between expand and the start of the source object specification.
src_owner.wildcard_name	src_owner is the owner of the source objects. Owner names cannot be wildcarded. If wildcards are used in the owner name, SharePlex assumes that they are part of the owner (schema) name. wildcard_name is the wildcarded name of the source objects. Rules: Oracle: The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners.
not (list)	An exclusion list that defines objects to omit from the wildcard expansion. Use this option to exclude objects that you do not want to be replicated. Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator. The not keyword and parentheses are required elements. list is a comma-separated list of tables owned by the same owner, either wildcarded or explicit. Example: not (spo%, gen%, product). Leave a space before and after the not keyword. A space is allowed after each comma in the list. Note: If an object that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples.
tgt_owner.wildcard_name	tgt_owner is the owner of the target objects. wildcard_name is the wildcarded name of the target objects. The target specification must be in the form of owner.%. Partially expanded target wildcarded names are not supported, such as owner.tab%.
routing_map	Any valid routing map. For more information, see Routing specifications in a configuration file

Validate a wildcard specification

To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the objects that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see the SharePlex Reference Guide.

Examples

Examples of valid wildcard specifications

Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. Note that SharePlex automatically upshifts the names, so that it actually activates all tables where the table name is like 'PROD%' but not like '%TEMP%'.

Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. For more information, see Configure named post queues.

Datasource:o.sidA	 	 
cust.%        cust.%        hostB@o.oraB
cust.photo    cust.photo    hostB:lobQ@o.oraB

The following are additional examples of valid wildcard specifications

Datasource:o.sidA	 	 
expand scott.%test%    scott.%    sysa@o.sidB

Datasource:o.sidA	 	 
expand scott.%t__t%    fred.%    sysa@o.sidB

Datasource:o.sidA	 	 
expand scott.% not (spo%, gen%, prodct)    scott.%     sysa@o.sidB

Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Examples of invalid wildcard specifications

The following example contains a wildcarded schema, which is not permitted.

Datasource:o.sidA	 	 
expand rob%.%test%    scott.%    sysa@o.sidB

The following example contains a partially wildcarded target object name, which is not permitted.

Datasource:o.sidA	 	 
expand scott.%test%    scott.%obj%    sysa@o.sidB

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem