DDL

Valid For

Extract and Replicat

Note: DDL replication is only supported between Oracle to Oracle databases and between MySQL to MySQL databases.

Description

Use the DDL parameter to:

When used without options, the DDL parameter performs no filtering, and it causes all DDL operations to be propagated as follows:

When used with options, the DDL parameter acts as a filtering agent to include or exclude DDL operations based on:

Only one DDL parameter can be used in a parameter file, but you can combine multiple inclusion and exclusion options to filter the DDL to the required level.

Note: Do not use the DDL parameter for an Extract data pump. These process types do not permit the mapping or conversion of DDL and will propagate DDL records automatically in pass-through mode. DDL that is performed on a source table (for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (`ALTER TABLE

                    TableA`). It``cannot be mapped as `ALTER

 

                    TABLE TableB`.
                    {: .infoboxnote}

For additional information about how to use Oracle GoldenGate DDL support, see Oracle: DDL Replication and MySQL: DDL Replication in Oracle GoldenGate Microservices Documentation.

Syntax

DDL [
{INCLUDE | EXCLUDE}
  [, MAPPED | UNMAPPED | OTHER | ALL]
  [, OPTYPE type]
  [, OBJTYPE 'type']
  [, SOURCECATALOG catalog | ALLCATALOGS]
  [, ALLOWEMPTYOBJECT]
  [, ALLOWEMPTYOWNER]
  [, OBJNAME name]
  [, INSTR 'string']
  [, INSTRWORDS 'word_list']
  [, INSTRCOMMENTS 'comment_string']
  [, INSTRCOMMENTSWORDS 'word_list']
  [, STAYMETADATA]
  [, EVENTACTIONS (action)
]
[...]

DDL Filtering Options

The following are the syntax options for filtering and operating upon the DDL that is replicated by Oracle GoldenGate. These options apply to the INCLUDE and EXCLUDE clauses of the DDL parameter and other parameters that support DDL replication.

**INCLUDE EXCLUDE**

Use INCLUDE or EXCLUDE to identify the beginning of an inclusion or exclusion clause.

The inclusion or exclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by any valid combination of the other filtering options of the DDL parameter.

If you use EXCLUDE, you must create a corresponding INCLUDE clause. For example, the following is invalid:

DDL EXCLUDE OBJNAME "hr".*

However, you can use either of the following:

DDL INCLUDE ALL, EXCLUDE OBJNAME "hr"."*"
DDL INCLUDE OBJNAME fin.* EXCLUDE OBJNAME "fin.ss"

An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multiple inclusion and exclusion clauses.

Do not include any Oracle GoldenGate installed DDL objects in a DDL parameter, in a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or MAPEXCLUDE parameter. Make certain that wildcard specifications in those parameters do not include Oracle GoldenGate-installed DDL objects. These objects must not be part of the Oracle GoldenGate configuration, but the Extract process must be aware of operations on them, and that is why you must not explicitly exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or MAPEXCLUDE parameter statement.

**MAPPED UNMAPPED OTHER ALL**

Use MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.

DDL EXCLUDE ALL is a special processing option that maintains up-to-date object metadata for Oracle GoldenGate, while blocking the replication of the DDL operations themselves. You can use DDL EXCLUDE ALL when using a method other than Oracle GoldenGate to apply DDL to the target, but you want Oracle GoldenGate to replicate data changes to the target objects. It provides the current metadata to Oracle GoldenGate as objects change, thus preventing the need to stop and start the Oracle GoldenGate processes. The following special conditions apply to DDL EXCLUDE ALL:

OPTYPE type

Use OPTYPE to apply INCLUDE or EXCLUDE to a specific type of DDL operation, such as CREATE, ALTER, and RENAME. For type, use any DDL command that is valid for the database. For example, to include ALTER operations, the correct syntax is:

DDL INCLUDE OPTYPE ALTER

OBJTYPE ‘type’

Use OBJTYPE to apply INCLUDE or EXCLUDE to a specific type of database object. For type, use any object type that is valid for the database, such as TABLE, INDEX, and TRIGGER. For an Oracle materialized view and materialized views log, the correct types are snapshot and snapshot log, respectively. Enclose the name of the object type within single quotes. For example:

DDL INCLUDE OBJTYPE 'INDEX'
DDL INCLUDE OBJTYPE 'SNAPSHOT'

For Oracle object type USER, do not use the OBJNAME option, because OBJNAME expects owner.object or container.owner.object whereas USER only has a schema.

*SOURCECATALOG *catalog ALLCATALOGS**

Use these options to specify how unqualified object names in an OBJNAME clause are resolved to the correct container. Use these options when the source database is an Oracle container database.

SOURCECATALOG specifies a default container for all of the object names that are specified in the same INCLUDE or EXCLUDE clause. To take effect, SOURCECATALOG must be specified before the OBJNAME specification. See “SOURCECATALOG” for more information including using statements that contain two-part names, where three-part object names are required to fully identify an object.

ALLCATALOGS specifies that all of the containers of the database should be considered when resolving object names that are specified in the same INCLUDE or EXCLUDE clause. ALLCATALOGS can be placed before or after the OBJNAME specification.

The following is the order of precedence that is given when there are different catalog specifications in a parameter file:

  1. ALLCATALOGS in an INCLUDE or EXCLUDE clause overrides all SOURCECATALOG specifications in the INCLUDE or EXCLUDE clause and at the root of the parameter file, and it overrides the container specification of a fully qualified object name in the OBJNAME clause.

  2. An explicit catalog specification in the OBJNAME clause overrides all instances of SOURCECATALOG (but not ALLCATALOGS).

  3. SOURCECATALOG in an INCLUDE or EXCLUDE clause overrides the global SOURCECATALOG parameter that is specified at the root of the TABLE or MAP statement.

  4. The global SOURCECATALOG parameter takes effect for any unqualified object names in OBJNAME clauses if the INCLUDE or EXCLUDE clause does not specify SOURCECATALOG or ALLCATALOGS.

  5. In the absence of any of the preceding parameters, all catalogs are considered.

ALLOWEMPTYOBJECT

Use ALLOWEMPTYOBJECT to allow an OBJNAME specification to process DDL that contains no object name. For example:

DDL INCLUDE OBJNAME sch.* ALLOWEMPTYOBJECT

ALLOWEMPTYOWNER

Use ALLOWEMPTYOWNER to allow an OBJNAME specification to process DDL that contains no owner name. For example:

DDL INCLUDE OBJNAME pdb.sch.* ALLOWEMPTYOWNER

OBJNAME name

Use OBJNAME to apply INCLUDE or EXCLUDE to the fully qualified name of an object. To specify two-part and three-part object names and wildcards correctly, see Using Wildcards in Command Arguments.

Enclose case-sensitive object names within double quote marks.

Case-insensitive example:

DDL INCLUDE OBJNAME accounts.*

Case-sensitive example:

DDL INCLUDE OBJNAME accounts."cust"

Do not use OBJNAME for the Oracle USER object, because OBJNAME expects owner.object or container.owner.object, whereas USER only has a schema.

When using OBJNAME with MAPPED in a Replicat parameter file, the value for OBJNAME must refer to the name specified with the TARGET clause of the MAP statement. For example, given the following MAP statement, the correct value is OBJNAME fin2.*.

MAP fin.exp_*, TARGET fin2.*;

In the following example, a CREATE TABLE statement executes as follows on the source:

CREATE TABLE fin.exp_phone;

That same statement executes as follows on the target:

CREATE TABLE fin2.exp_phone;

If a target owner is not specified in the MAP statement, Replicat maps it to the database user that is specified with the USERIDALIAS parameter.

For DDL that creates derived objects, such as a trigger, the value for OBJNAME must be the name of the base object, not the name of the derived object.

For example, to include the following DDL statement, the correct value is hr.accounts, not hr.insert_trig.

CREATE TRIGGER hr.insert_trig ON hr.accounts;

For RENAME operations, the value for OBJNAME must be the new table name. For example, to include the following DDL statement, the correct value is hr.acct.

ALTER TABLE hr.accounts RENAME TO acct;

INSTR ‘string’

Use INSTR to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within the command syntax itself, but not within comments. For example, the following excludes DDL that creates an index.

DDL INCLUDE ALL EXCLUDE INSTR 'CREATE INDEX'

Enclose the string within single quotes. The string search is not case sensitive.

INSTR does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRCOMMENTS ‘comment_string’

(Valid for Oracle) Use INSTRCOMMENTS to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within a comment, but not within the DDL command itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

For example, the following excludes DDL statements that include the string ‘source only’ in the comments.

DDL INCLUDE ALL EXCLUDE INSTRCOMMENTS 'SOURCE ONLY'

In this example, DDL statements such as the following are not replicated.

CREATE USER john IDENTIFIED BY john /source only/;

Enclose the string within single quotes. The string search is not case sensitive. You can combine INSTR and INSTRCOMMENTS to filter on a string in the command syntax and in the comments of the same DDL statement.

INSTRCOMMENTS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRWORDS ‘word_list’

Use INSTRWORDS to apply INCLUDE or EXCLUDE to DDL statements that contain the specified words.

For word_list, supply the words in any order, within single quotes. To include spaces, put the space (and the word, if applicable) in double quotes. Double quotes also can be used to enclose sentences.

All specified words must be present in the DDL for INSTRWORDS to take effect.

Example:

DDL INCLUDE OPTYPE ALTER OBJTEYP 'TABLE' INSTRWORDS 'ALTER CONSTRAINT " xyz"'

This example matches the following DDL statements:

ALTER TABLE ADD CONSTRAINT xyz CHECK
ALTER TABLE DROP CONSTRAINT xyz

INSTRWORDS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRCOMMENTSWORDS ‘word_list’

(Valid for Oracle) Works the same way as INSTRWORDS, but only applies to comments within a DDL statement, not the DDL syntax itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

INSTRCOMMENTSWORDS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

You can combine INSTRWORDS and INSTRCOMMENTSWORDS to filter on a string in the command syntax and in the comments of the same DDL statement.

STAYMETADATA

(Valid for Oracle). Prevents metadata from being captured by Extract or applied by Replicat.

When Extract first encounters DML on a table, it retrieves the metadata for that table. When DDL is encountered on that table, the old metadata is invalidated. The next DML on that table is matched to the new metadata so that the target table structure always is up-to-date with that of the source.

However, if you know that a particular DDL operation will not affect the table’s metadata, you can use STAYMETADATA so that the current metadata is not retrieved or replicated. This is a performance improvement that has benefit for such operations as imports and exports, where such DDL as truncates and the disabling of constraints are often performed. These operations do not affect table structure, as it relates to the integrity of subsequent data replication, so they can be ignored in such cases. For example ALTER TABLE ADD FOREIGN KEY does not affect table metadata.

An example of how this can be applied selectively is as follows:

DDL INCLUDE ALL INCLUDE STAYMETADATA OBJNAME xyz

This example states that all DDL is to be included for replication, but only DDL that operates on object xyz will be subject to STAYMETADATA.

STAYMETADATA also can be used the same way in an EXCLUDE clause.

STAYMETADATA must be used the same way on the source and target to ensure metadata integrity.

When STAYMETADATA is in use, a message is added to the report file. DDL reporting is controlled by the DDLOPTIONS parameter with the REPORT option.

This same functionality can be applied globally to all DDL that occurs on the source by using the @ddl_staymetadata scripts:

This option should be used with the assistance of Oracle GoldenGate technical support staff, because it might not always be apparent which DDL affects object metadata. If improperly used, it can compromise the integrity of the replication environment.

EVENTACTIONS (action)

Causes the Extract or Replicat process take a defined action based on a DDL record in the transaction log or trail, which is known as the event record. The DDL event is triggered if the DDL record is eligible to be written to the trail by Extract or a data pump, or to be executed by Replicat, as determined by the other filtering options of the DDL parameter. You can use this system to customize processing based on database events.

For action, see EVENTACTIONS under the MAP and TABLE parameters.

Guidelines for using EVENTACTIONS on DDL records:

EVENTACTIONS does not support the following DDL objects because they are derived objects:

Examples

Example 1 Combining DDL Parameter Options

The following is an example of how to combine the options of the DDL parameter.

DDL  &
INCLUDE UNMAPPED &
    OPTYPE alter &
    OBJTYPE 'table' &
    OBJNAME users.tab* &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME temporary.tab

The combined filter criteria in this statement specify the following:

Example 2 Including an Event Action

The following example specifies an event action of REPORT for all DDL records.

DDL INCLUDE ALL EVENTACTIONS (REPORT)

Example 3 Using an Event Action on a Subset of DDL

The following example shows how EVENTACTIONS can be used on a subset of the DDL. All DDL is to be replicated, but only the DDL that is executed on explicitly named objects qualifies to trigger the event actions of REPORT and LOG.

DDL INCLUDE ALL &
    INCLUDE OBJNAME sales.t* EVENTACTIONS (REPORT) &
    INCLUDE OBJNAME fin.my_tab EVENTACTIONS (LOG INFO)

Example 4

The following example demonstrates the different ways to specify catalog names for DDL that is issued on objects in a source Oracle container database.

SOURCECATALOG pdb1
DDL INCLUDE OBJNAME sch1.obj1 INCLUDE SOURCECATALOG pdb2 OBJNAME sch2.obj2
DDL INCLUDE ALLCATALOGS OBJNAME sch.obj
DDL INCLUDE ALLCATALOGS OBJNAME pdb.sch.obj

Example 5

The following shows the combined use of ALLOWEMPTYOBJECT and ALLOWEMPTYOWNER.

DDL INCLUDE pdb.*.* ALLOWEMPTYOWNER ALLOWEMPTYOBJECT

description: keywords: —

DDL

Valid For

Extract and Replicat

Note: DDL replication is only supported between Oracle to Oracle databases and between MySQL to MySQL databases.

Description

Use the DDL parameter to:

When used without options, the DDL parameter performs no filtering, and it causes all DDL operations to be propagated as follows:

When used with options, the DDL parameter acts as a filtering agent to include or exclude DDL operations based on:

Only one DDL parameter can be used in a parameter file, but you can combine multiple inclusion and exclusion options to filter the DDL to the required level.

Note: Do not use the DDL parameter for an Extract data pump. These process types do not permit the mapping or conversion of DDL and will propagate DDL records automatically in pass-through mode. DDL that is performed on a source table (for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (ALTER TABLE TableA). It cannot be mapped as ALTER TABLE TableB.

For additional information about how to use Oracle GoldenGate DDL support, see Oracle: DDL Replication and MySQL: DDL Replication in Oracle GoldenGate Microservices Documentation.

Syntax

DDL [
{AUTOSCHEMA}   
{INCLUDE | EXCLUDE}
  [, ALLOWEMPTYOBJECT]
  [, ALLOWEMPTYOWNER]
  [, MAPPED | UNMAPPED | OTHER | ALL]
  [, OPTYPE type]
  [, OBJTYPE 'type']
  [, OBJNAME name]
  [, INSTR 'string']
  [, INSTRWORDS 'word_list']
  [, INSTRCOMMENTS 'comment_string']
  [, INSTRCOMMENTSWORDS 'word_list']
  [, SOURCECATALOG catalog | ALLCATALOGS]
  [, STAYMETADATA]
  [, EVENTACTIONS (action)
]
[...]

AUTOSCHEMA

Valid for Replicat in MySQL to Oracle, Oracle to MySQL, and MySQL to MySQL data replication.

The DDL AUTOSCHEMA parameter option is used to enable the automatic schema evolution functionality. By default, this option is disabled.

Syntax

DDL AUTOSCHEMA

Only MAPPED DDL operations are relevant for Automatic Schema Evolution. Therefore, the object names specified as part of OBJNAME clause in the inclusion/exclusion criteria are supposed to correspond with target table names (and not the source table names).

When using OBJNAME with MAPPED in a Replicat parameter file, the value for OBJNAME must refer to the name specified with the TARGET clause of the MAP statement.

For details, see Using Automatic Schema Evolution

INCLUDE | EXCLUDE

DDL Filtering Options

The following syntax options allow filtering and operating upon the DDL that is replicated by Oracle GoldenGate. These options apply to the INCLUDE and EXCLUDE clauses of the DDL parameter and other parameters that support DDL replication.

Use INCLUDE or EXCLUDE to identify the beginning of an inclusion or exclusion clause.

The inclusion or exclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by any valid combination of the other filtering options of the DDL parameter.

If you use EXCLUDE, you must create a corresponding INCLUDE clause. For example, the following is invalid:

DDL EXCLUDE OBJNAME "hr".*

However, you can use either of the following:

DDL INCLUDE ALL, EXCLUDE OBJNAME "hr"."*"
DDL INCLUDE OBJNAME fin.* EXCLUDE OBJNAME "fin.ss"

An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multiple inclusion and exclusion clauses.

Do not include any Oracle GoldenGate installed DDL objects in a DDL parameter, in a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or MAPEXCLUDE parameter. Make certain that wildcard specifications in those parameters do not include Oracle GoldenGate-installed DDL objects. These objects must not be part of the Oracle GoldenGate configuration, but the Extract process must be aware of operations on them, and that is why you must not explicitly exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or MAPEXCLUDE parameter statement.

ALLOWEMPTYOBJECT

Use ALLOWEMPTYOBJECT to allow an OBJNAME specification to process DDL that contains no object name. For example:

DDL INCLUDE OBJNAME sch.* ALLOWEMPTYOBJECT

ALLOWEMPTYOWNER

Use ALLOWEMPTYOWNER to allow an OBJNAME specification to process DDL that contains no owner name. For example:

DDL INCLUDE OBJNAME pdb.sch.* ALLOWEMPTYOWNER

EVENTACTIONS (action)

Causes the Extract or Replicat process take a defined action based on a DDL record in the transaction log or trail, which is known as the event record. The DDL event is triggered if the DDL record is eligible to be written to the trail by Extract or a data pump, or to be executed by Replicat, as determined by the other filtering options of the DDL parameter. You can use this system to customize processing based on database events.

For action, see EVENTACTIONS under the MAP and TABLE parameters.

Guidelines for using EVENTACTIONS on DDL records:

EVENTACTIONS does not support the following DDL objects because they are derived objects:

MAPPED | UNMAPPED | OTHER | ALL

Use MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.

DDL EXCLUDE ALL is a special processing option that maintains up-to-date object metadata for Oracle GoldenGate, while blocking the replication of the DDL operations themselves. You can use DDL EXCLUDE ALL when using a method other than Oracle GoldenGate to apply DDL to the target, but you want Oracle GoldenGate to replicate data changes to the target objects. It provides the current metadata to Oracle GoldenGate as objects change, thus preventing the need to stop and start the Oracle GoldenGate processes. The following special conditions apply to DDL EXCLUDE ALL:

OBJNAME name

Use OBJNAME to apply INCLUDE or EXCLUDE to the fully qualified name of an object. To specify two-part and three-part object names and wildcards correctly, see Using Wildcards in Command Arguments.

Enclose case-sensitive object names within double quote marks.

Case-insensitive example:

DDL INCLUDE OBJNAME accounts.*

Case-sensitive example:

DDL INCLUDE OBJNAME accounts."cust"

Do not use OBJNAME for the Oracle USER object, because OBJNAME expects owner.object or container.owner.object, whereas USER only has a schema.

When using OBJNAME with MAPPED in a Replicat parameter file, the value for OBJNAME must refer to the name specified with the TARGET clause of the MAP statement. For example, given the following MAP statement, the correct value is OBJNAME fin2.*.

MAP fin.exp_*, TARGET fin2.*;

In the following example, a CREATE TABLE statement executes as follows on the source:

CREATE TABLE fin.exp_phone;

That same statement executes as follows on the target:

CREATE TABLE fin2.exp_phone;

If a target owner is not specified in the MAP statement, Replicat maps it to the database user that is specified with the USERIDALIAS parameter.

For DDL that creates derived objects, such as a trigger, the value for OBJNAME must be the name of the base object, not the name of the derived object.

For example, to include the following DDL statement, the correct value is hr.accounts, not hr.insert_trig.

CREATE TRIGGER hr.insert_trig ON hr.accounts;

For RENAME operations, the value for OBJNAME must be the new table name. For example, to include the following DDL statement, the correct value is hr.acct.

ALTER TABLE hr.accounts RENAME TO acct;

OBJTYPE type

Use OBJTYPE to apply INCLUDE or EXCLUDE to a specific type of database object. For type, use any object type that is valid for the database, such as TABLE, INDEX, and TRIGGER. For an Oracle materialized view and materialized views log, the correct types are snapshot and snapshot log, respectively. Enclose the name of the object type within single quotes. For example:

DDL INCLUDE OBJTYPE 'INDEX'
DDL INCLUDE OBJTYPE 'SNAPSHOT'

For Oracle object type USER, do not use the OBJNAME option, because OBJNAME expects owner.object or container.owner.object whereas USER only has a schema.

OPTYPE type

Use OPTYPE to apply INCLUDE or EXCLUDE to a specific type of DDL operation, such as CREATE, ALTER, and RENAME. For type, use any DDL command that is valid for the database. For example, to include ALTER operations, the correct syntax is:

DDL INCLUDE OPTYPE ALTER

INSTR string

Use INSTR to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within the command syntax itself, but not within comments. For example, the following excludes DDL that creates an index.

DDL INCLUDE ALL EXCLUDE INSTR 'CREATE INDEX'

Enclose the string within single quotes. The string search is not case sensitive.

INSTR does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRCOMMENTS comment_string

(Valid for Oracle) Use INSTRCOMMENTS to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within a comment, but not within the DDL command itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

For example, the following excludes DDL statements that include the string ‘source only’ in the comments.

DDL INCLUDE ALL EXCLUDE INSTRCOMMENTS 'SOURCE ONLY'

In this example, DDL statements such as the following are not replicated.

CREATE USER john IDENTIFIED BY john /source only/;

Enclose the string within single quotes. The string search is not case sensitive. You can combine INSTR and INSTRCOMMENTS to filter on a string in the command syntax and in the comments of the same DDL statement.

INSTRCOMMENTS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRWORDS word_list

Use INSTRWORDS to apply INCLUDE or EXCLUDE to DDL statements that contain the specified words.

For word_list, supply the words in any order, within single quotes. To include spaces, put the space (and the word, if applicable) in double quotes. Double quotes also can be used to enclose sentences.

All specified words must be present in the DDL for INSTRWORDS to take effect.

Example:

DDL INCLUDE OPTYPE ALTER OBJTEYP 'TABLE' INSTRWORDS 'ALTER CONSTRAINT " xyz"'

This example matches the following DDL statements:

ALTER TABLE ADD CONSTRAINT xyz CHECK
ALTER TABLE DROP CONSTRAINT xyz

INSTRWORDS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

INSTRCOMMENTSWORDS word_list

(Valid for Oracle) Works the same way as INSTRWORDS, but only applies to comments within a DDL statement, not the DDL syntax itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

INSTRCOMMENTSWORDS does not support single quotation marks (‘ ‘) that are within the string, nor does it support NULL values.

You can combine INSTRWORDS and INSTRCOMMENTSWORDS to filter on a string in the command syntax and in the comments of the same DDL statement.

**SOURCECATALOG catalog ALLCATALOGS**

Use these options to specify how unqualified object names in an OBJNAME clause are resolved to the correct container. Use these options when the source database is an Oracle container database.

SOURCECATALOG specifies a default container for all of the object names that are specified in the same INCLUDE or EXCLUDE clause. To take effect, SOURCECATALOG must be specified before the OBJNAME specification. See “SOURCECATALOG” for more information including using statements that contain two-part names, where three-part object names are required to fully identify an object.

ALLCATALOGS specifies that all of the containers of the database should be considered when resolving object names that are specified in the same INCLUDE or EXCLUDE clause. ALLCATALOGS can be placed before or after the OBJNAME specification.

The following is the order of precedence that is given when there are different catalog specifications in a parameter file:

  1. ALLCATALOGS in an INCLUDE or EXCLUDE clause overrides all SOURCECATALOG specifications in the INCLUDE or EXCLUDE clause and at the root of the parameter file, and it overrides the container specification of a fully qualified object name in the OBJNAME clause.

  2. An explicit catalog specification in the OBJNAME clause overrides all instances of SOURCECATALOG (but not ALLCATALOGS).

  3. SOURCECATALOG in an INCLUDE or EXCLUDE clause overrides the global SOURCECATALOG parameter that is specified at the root of the TABLE or MAP statement.

  4. The global SOURCECATALOG parameter takes effect for any unqualified object names in OBJNAME clauses if the INCLUDE or EXCLUDE clause does not specify SOURCECATALOG or ALLCATALOGS.

  5. In the absence of any of the preceding parameters, all catalogs are considered.

STAYMETADATA

(Valid for Oracle). Prevents metadata from being captured by Extract or applied by Replicat.

When Extract first encounters DML on a table, it retrieves the metadata for that table. When DDL is encountered on that table, the old metadata is invalidated. The next DML on that table is matched to the new metadata so that the target table structure always is up-to-date with that of the source.

However, if you know that a particular DDL operation will not affect the table’s metadata, you can use STAYMETADATA so that the current metadata is not retrieved or replicated. This is a performance improvement that has benefit for such operations as imports and exports, where such DDL as truncates and the disabling of constraints are often performed. These operations do not affect table structure, as it relates to the integrity of subsequent data replication, so they can be ignored in such cases. For example ALTER TABLE ADD FOREIGN KEY does not affect table metadata.

An example of how this can be applied selectively is as follows:

DDL INCLUDE ALL INCLUDE STAYMETADATA OBJNAME xyz

This example states that all DDL is to be included for replication, but only DDL that operates on object xyz will be subject to STAYMETADATA.

STAYMETADATA also can be used the same way in an EXCLUDE clause.

STAYMETADATA must be used the same way on the source and target to ensure metadata integrity.

When STAYMETADATA is in use, a message is added to the report file. DDL reporting is controlled by the DDLOPTIONS parameter with the REPORT option.

This same functionality can be applied globally to all DDL that occurs on the source by using the @ddl_staymetadata scripts:

This option should be used with the assistance of Oracle GoldenGate technical support staff, because it might not always be apparent which DDL affects object metadata. If improperly used, it can compromise the integrity of the replication environment.

Examples

Following are few examples of different use cases:

Example 1: Using DDL AUTOSCHEMA

Example 2: Combining DDL Parameter Options

The following is an example of how to combine the options of the DDL parameter.

DDL  &
INCLUDE UNMAPPED &
    OPTYPE alter &
    OBJTYPE 'table' &
    OBJNAME users.tab* &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME temporary.tab

The combined filter criteria in this statement specify the following:

Example 3 Including an Event Action

The following example specifies an event action of REPORT for all DDL records.

DDL INCLUDE ALL EVENTACTIONS (REPORT)

Example 4 Using an Event Action on a Subset of DDL

The following example shows how EVENTACTIONS can be used on a subset of the DDL. All DDL is to be replicated, but only the DDL that is executed on explicitly named objects qualifies to trigger the event actions of REPORT and LOG.

DDL INCLUDE ALL &
    INCLUDE OBJNAME sales.t* EVENTACTIONS (REPORT) &
    INCLUDE OBJNAME fin.my_tab EVENTACTIONS (LOG INFO)

Example 5

The following example demonstrates the different ways to specify catalog names for DDL that is issued on objects in a source Oracle container database.

SOURCECATALOG pdb1
DDL INCLUDE OBJNAME sch1.obj1 INCLUDE SOURCECATALOG pdb2 OBJNAME sch2.obj2
DDL INCLUDE ALLCATALOGS OBJNAME sch.obj
DDL INCLUDE ALLCATALOGS OBJNAME pdb.sch.obj

Example 6

The following shows the combined use of ALLOWEMPTYOBJECT and ALLOWEMPTYOWNER.

DDL INCLUDE pdb.*.* ALLOWEMPTYOWNER ALLOWEMPTYOBJECT