3.153 SCHEMAEXCLUDE

Valid For

Extract, Replicat, DEFGEN

Description

Use the SCHEMAEXCLUDE parameter to exclude source objects that are owned by the specified source owner (such as a schema) from the Oracle GoldenGate configuration when wildcards are being used to specify the owners in TABLE or MAP statements. This parameter is valid for two- and three-part names.

Wildcards can be used for the optional catalog or container specification, as well as the schema specification. Make certain not to use wildcards such that all objects are excluded. Follow the rules for using wildcards in Administering Oracle GoldenGate for Windows and UNIX.

The positioning of SCHEMAEXCLUDE in relation to parameters that specify files or trails determines its effect. Parameters that specify trails or files are: EXTFILE, RMTFILE, EXTTRAIL, RMTTRAIL. The parameter works as follows:

  • When a SCHEMAEXCLUDE specification is placed before any TABLE or SEQUENCE parameters, and also before the parameters that specify trails or files, it applies globally to all trails or files, and to all TABLE and SEQUENCE parameters.

  • When a SCHEMAEXCLUDE specification is placed after a parameter that specifies a trail or file, it is effective only for that trail or file and only for the TABLE or SEQUENCE parameters that are associated with it. Multiple trail or file specifications can be made in a parameter file, each followed by a set of TABLE, SEQUENCE, and TABLEEXCLUDE specifications.

SCHEMAEXCLUDE is evaluated before evaluating the associated TABLE or SEQUENCE parameters. Thus, the order in which they appear does not make a difference.

See also the EXCLUDEWILDCARDOBJECTSONLY parameter.

Default

None

Syntax

SCHEMAEXCLUDE [container. | catalog.]schema
container. | catalog.

If the database requires three-part names, specifies the source Oracle container or SQL/MX catalog that contains the source owner that is to be excluded. Use if a qualifier is required to identify the correct owner to exclude.

schema

Specifies the name of the source owner that is to be excluded. For databases that require three-part names, you can use schema without catalog if the SCHEMAEXCLUDE specification precedes a set of TABLE or MAP parameters for which the default container or catalog is specified with the SOURCECATALOG parameter, or if the SQL/MX catalog is defined by the SOURCEDB or TARGETDB parameter.

Examples

Example 1   

This example excludes the source test* schemas. Note that it omits the catalog specification from the owner name because the catalog is specified with the SOURCEDB parameter in a SQL/MX configuration.

EXTRACT capt
SOURCEDB catalog1, USERID schema1
RMTHOST sysb, MGRPORT 7809
RMTTRAIL /ggs/dirdat/aa
SCHEMAEXCLUDE test*
TABLE *.*;
Example 2   

This Oracle example requires both catalog (container) and schema specifications and demonstrates how wildcards can be used as part of the specification.

EXTRACT capt
USERIDALIAS alias1
RMTHOST sysb, MGRPORT 7809
RMTTRAIL /ggs/dirdat/aa
SCHEMAEXCLUDE pdbtest.test*
TABLE pdb*.*.*;
Example 3   

This example shows how to use SCHEMAEXCLUDE when the database requires only a two-part name.

TABLE abc*.*;
SCHEMAEXCLUDE abctest*