DDL
Valid For
Note:
DDL replication is only supported between Oracle to Oracle databases and between MySQL to MySQL databases.Description
Use the DDL
parameter to:
-
enable DDL support
-
filter DDL operations
-
configure a processing action based on a DDL record
When used without options, the DDL
parameter performs no filtering, and it causes all DDL operations to be propagated as follows:
-
As an Extract parameter, it captures all supported DDL operations that are generated on all supported database objects and sends them to the trail.
-
As a Replicat parameter, it replicates all DDL operations from the Oracle GoldenGate trail and applies them to the target. This is the same as the default behavior without this parameter.
When used with options, the DDL
parameter acts as a filtering agent to include or exclude DDL operations based on:
-
scope
-
object type
-
operation type
-
object name
-
strings in the DDL command syntax or comments, or both
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.
-
The filtering options of the
DDL
parameter are valid for a primary Extract that captures from the transaction source, but not for a data-pump Extract. -
When combined, multiple filter option specifications are linked logically as
AND
statements. -
All filter criteria specified with multiple options must be satisfied for a DDL statement to be replicated.
-
When using complex filtering criteria in a
DDL
parameter statement, it is recommended that you test your configuration in a test environment before using it in production. -
See Example 1, "Example" for more information.
Note:
Do not use theDDL
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 [ {INCLUDE | EXCLUDE} [, MAPPED | UNMAPPED | OTHER | ALL] [, OPTYPE type] [, OBJTYPE 'type'] [, SOURCECATALOGcatalog
| 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
orEXCLUDE
to identify the beginning of an inclusion or exclusion clause.-
An inclusion clause contains filtering criteria that identifies the DDL that this parameter will affect.
-
An exclusion clause contains filtering criteria that excludes specific DDL from this parameter.
The inclusion or exclusion clause must consist of the
INCLUDE
orEXCLUDE
keyword followed by any valid combination of the other filtering options of theDDL
parameter.If you use
EXCLUDE
, you must create a correspondingINCLUDE
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 anyINCLUDEs
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 aTABLE
parameter, or in aMAP
parameter, nor in aTABLEEXCLUDE
orMAPEXCLUDE
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 anEXCLUDE
,TABLEEXCLUDE
, orMAPEXCLUDE
parameter statement.-
MAPPED | UNMAPPED | OTHER | ALL
-
Use
MAPPED
,UNMAPPED
,OTHER
, andALL
to applyINCLUDE
orEXCLUDE
based on the DDL operation scope.-
MAPPED
appliesINCLUDE
orEXCLUDE
to DDL operations that are ofMAPPED
scope.MAPPED
filtering is performed before filtering that is specified with otherDDL
parameter options. -
UNMAPPED
appliesINCLUDE
orEXCLUDE
to DDL operations that are ofUNMAPPED
scope. -
OTHER
appliesINCLUDE
orEXCLUDE
to DDL operations that are ofOTHER
scope. -
ALL
appliesINCLUDE
orEXCLUDE
to DDL operations of all scopes.
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 useDDL 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 toDDL EXCLUDE ALL
:-
DDL EXCLUDE ALL
does not require the use of anINCLUDE
clause. -
When using
DDL EXCLUDE ALL
, you can set theWILDCARDRESOLVE
parameter toIMMEDIATE
to allow immediate DML resolution if required.
-
-
OPTYPE
type
-
Use
OPTYPE
to applyINCLUDE
orEXCLUDE
to a specific type of DDL operation, such asCREATE
,ALTER
, andRENAME
. Fortype
, use any DDL command that is valid for the database. For example, to includeALTER
operations, the correct syntax is:DDL INCLUDE OPTYPE ALTER
-
OBJTYPE '
type'
-
Use
OBJTYPE
to applyINCLUDE
orEXCLUDE
to a specific type of database object. Fortype
, use any object type that is valid for the database, such asTABLE
,INDEX
, andTRIGGER
. For an Oracle materialized view and materialized views log, the correct types aresnapshot
andsnapshot 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 theOBJNAME
option, becauseOBJNAME
expectsowner.object
orcontainer.owner.object
whereasUSER
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 sameINCLUDE
orEXCLUDE
clause. To take effect,SOURCECATALOG
must be specified before theOBJNAME
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 sameINCLUDE
orEXCLUDE
clause.ALLCATALOGS
can be placed before or after theOBJNAME
specification.The following is the order of precedence that is given when there are different catalog specifications in a parameter file:
-
ALLCATALOGS
in anINCLUDE
orEXCLUDE
clause overrides allSOURCECATALOG
specifications in theINCLUDE
orEXCLUDE
clause and at the root of the parameter file, and it overrides the container specification of a fully qualified object name in theOBJNAME
clause. -
An explicit catalog specification in the
OBJNAME
clause overrides all instances ofSOURCECATALOG
(but notALLCATALOGS
). -
SOURCECATALOG
in anINCLUDE
orEXCLUDE
clause overrides the globalSOURCECATALOG
parameter that is specified at the root of theTABLE
orMAP
statement. -
The global
SOURCECATALOG
parameter takes effect for any unqualified object names inOBJNAME
clauses if theINCLUDE
orEXCLUDE
clause does not specifySOURCECATALOG
orALLCATALOGS
. -
In the absence of any of the preceding parameters, all catalogs are considered.
-
-
ALLOWEMPTYOBJECT
-
Use
ALLOWEMPTYOBJECT
to allow anOBJNAME
specification to process DDL that contains no object name. For example:DDL INCLUDE OBJNAME sch.* ALLOWEMPTYOBJECT
-
ALLOWEMPTYOWNER
-
Use
ALLOWEMPTYOWNER
to allow anOBJNAME
specification to process DDL that contains no owner name. For example:DDL INCLUDE OBJNAME pdb.sch.* ALLOWEMPTYOWNER
-
OBJNAME
name
-
Use
OBJNAME
to applyINCLUDE
orEXCLUDE
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 OracleUSER
object, becauseOBJNAME
expectsowner.object
orcontainer.owner.object
, whereasUSER
only has a schema.When using
OBJNAME
withMAPPED
in a Replicat parameter file, the value forOBJNAME
must refer to the name specified with theTARGET
clause of theMAP
statement. For example, given the followingMAP
statement, the correct value isOBJNAME 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 theUSERIDALIAS
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
, nothr.insert_trig
.CREATE TRIGGER hr.insert_trig ON hr.accounts;
For
RENAME
operations, the value forOBJNAME
must be the new table name. For example, to include the following DDL statement, the correct value ishr.acct
.ALTER TABLE hr.accounts RENAME TO acct;
-
INSTR '
string'
-
Use
INSTR
to applyINCLUDE
orEXCLUDE
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 supportNULL
values. -
INSTRCOMMENTS '
comment_string'
-
(Valid for Oracle) Use
INSTRCOMMENTS
to applyINCLUDE
orEXCLUDE
to DDL statements that contain a specific character string within a comment, but not within the DDL command itself. By usingINSTRCOMMENTS
, 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
andINSTRCOMMENTS
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 supportNULL
values. -
INSTRWORDS '
word_list'
-
Use
INSTRWORDS
to applyINCLUDE
orEXCLUDE
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 supportNULL
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 usingINSTRCOMMENTS
, you can use comments as a filtering agent.INSTRCOMMENTSWORDS
does not support single quotation marks (' ') that are within the string, nor does it supportNULL
values.You can combine
INSTRWORDS
andINSTRCOMMENTSWORDS
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 exampleALTER 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 toSTAYMETADATA
.STAYMETADATA
also can be used the same way in anEXCLUDE
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 theDDLOPTIONS
parameter with theREPORT
option.This same functionality can be applied globally to all DDL that occurs on the source by using the
@ddl_staymetadata
scripts:-
@ddl_staymetadata_on
globally turns off metadata versioning. -
@ddl_staymetadata_off
globally enables metadata versioning again.
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
, seeEVENTACTIONS
under theMAP
andTABLE
parameters.Guidelines for using
EVENTACTIONS
on DDL records:-
CHECKPOINTBEFORE
: Since each DDL record is autonomous, the DDL record is guaranteed to be the start of a transaction; therefore, theCHECKPOINT BEFORE
event action is implied for a DDL record. -
IGNORE
: This option is not valid for DDL records. Because DDL operations are autonomous, ignoring a record is equivalent to ignoring the entire transaction.
EVENTACTIONS
does not support the following DDL objects because they are derived objects:-
indexes
-
triggers
-
synonyms
-
RENAME
on a table andALTER TABLE RENAME
-
-
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:
-
INCLUDE
allALTER TABLE
statements for tables that are not mapped with aTABLE
orMAP
statement (UNMAPPED
scope), but only if those tables are owned byusers
and their names start withtab
, -
INCLUDE
all DDL operation types for all tables that are mapped with aTABLE
orMAP
statement (MAPPED
scope), -
EXCLUDE
all DDL operation types for all tables that areMAPPED
in scope, but only if those tables are owned bytemporary
and only if their names begin withtab
.
-
- 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 ofREPORT
andLOG
.DDL INCLUDE ALL & INCLUDE OBJNAME sales.t* EVENTACTIONS (REPORT) & INCLUDE OBJNAME fin.my_tab EVENTACTIONS (LOG) &
- 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.
-
This includes
pdb1.sch1.obj1
andpdb2.sch2.obj2
for DDL processing.SOURCECATALOG pdb1 DDL INCLUDE OBJNAME sch1.obj1 INCLUDE SOURCECATALOG pdb2 OBJNAME sch2.obj2
-
This includes all objects with the name
sch.obj
in any catalog for DDL processing.DDL INCLUDE ALLCATALOGS OBJNAME sch.obj
-
This also includes all objects with the name
sch.obj
in any catalog for DDL processing, becauseALLCATALOGS
overrides any other catalog specification.DDL INCLUDE ALLCATALOGS OBJNAME pdb.sch.obj
-
- Example 5
-
The following shows the combined use of
ALLOWEMPTYOBJECT
andALLOWEMPTYOWNER
.DDL INCLUDE pdb.*.* ALLOWEMPTYOWNER ALLOWEMPTYOBJECT