DDL Filtering in GoldenGate Extract and Replicat

When replicating data with GoldenGate, you often want DDL changes (table alterations, new tables, dropped indexes, etc.) to follow alongside DML. But replicating all DDL is sometimes dangerous (TRUNCATE, DROP TABLE) or simply impossible (architecture differences between source and target). DDL filtering gives you precise control over this.

This blog covers multiple aspects, including latest changes introduced in GoldenGate 26ai:

• Difference between extract and replicat filtering
• DDL filtering model and syntax
• AND/OR logic
• DDLOPTIONS parameter
• Tag-based filtering (EXCLUDETAG/INCLUDETAG)
• Edge cases

Extract vs Replicat DDL filtering

Before diving into the syntax, you should understand that DDL filtering means something different for an extract and a replicat.

For an extract, filtering controls what gets written to the trail file. If a DDL statement is excluded at the extract level, it is gone forever, and will never reach any downstream replicat. This makes extract-level filtering absolute and decisive. On the other side, it keeps trail files smaller and ensures no downstream replicat ever sees unwanted DDL.

For a replicat, filtering controls what gets applied to the target database. In that case, the DDL statement is considered to be already in the trail file. The replicat simply chooses to apply it or skip it. This gives more flexibility. For instance, multiple replicats can read the same trail and each apply a different subset of DDL. Of course, the trail files are larger, and generate more I/O.

This does not mean that you should choose one or another. A common use case is to do generic filtering at the extract level (removing clearly irrelevant DDL like CREATE USER or GRANT), and more targeted filtering for each replicat (for example, suppressing TRUNCATE on a target holding additional rows).

Enabling DDL Replication

DDL replication is disabled by default. You must explicitly enable it in both extract and replicat parameter files.

The simplest form is:

DDL

This enables DDL replication with no filtering. All DDL is captured (extract) or applied (replicat). In practice this is almost never what you want. You will almost always pair it with INCLUDE or EXCLUDE clauses.

Scopes: MAPPED, UNMAPPED, OTHER, ALL

Every DDL statement GoldenGate encounters is classified into one of four scopes based on whether the object it operates on matches your TABLE (extract) or MAP (replicat) parameter list.

If you do not specify a scope, GoldenGate defaults to ALL.

Example

With this extract parameter file:

EXTRACT EXT
USERIDALIAS source_cdb DOMAIN OracleGoldenGate
EXTTRAIL pdb1/aa
SOURCECATALOG PDB1
DDL INCLUDE ALL
TABLE APP_SCHEMA.*;

• CREATE TABLE APP_SCHEMA.ORDERS (...) is MAPPED (matches APP_SCHEMA.*)
• CREATE TABLE AUDIT_SCHEMA.LOGS (...) is UNMAPPED (does not match APP_SCHEMA.*)
• GRANT SELECT ON APP_SCHEMA.ORDERS TO READONLY is MAPPED (a GRANT on a specific object follows that object’s own scope, not OTHER. OTHER is only for DDL with no target object at all, like CREATE USER)

Switching to DDL INCLUDE MAPPED would capture the first and third statements.

The DDL statement’s scope depends on the operation: see “Interaction with TABLE and MAP Parameters” below for more details.

Filter Keywords

On top of scope filtering, you can also filter DDL by object type, operation type, object name, and DDL text content. These filters are specified as comma-separated conditions within a single DDL clause. All conditions in a single clause are added together (logical AND).

OBJTYPE

Filters by the type of database object the DDL operates on.

Common Oracle object types: TABLE, INDEX, SEQUENCE, VIEW, PROCEDURE, FUNCTION, TRIGGER, PACKAGE, TYPE, SYNONYM, DATABASE LINK, TABLESPACE, USER.

-- Only capture DDL on tables
DDL INCLUDE MAPPED, OBJTYPE 'TABLE'

-- Only capture DDL on indexes
DDL INCLUDE MAPPED, OBJTYPE 'INDEX'

OPTYPE

Filters by the type of DDL operation.

Common operation types: CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE, COMMENT.

-- Only capture CREATE TABLE statements
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE

-- Exclude DROP operations on all object types
DDL EXCLUDE MAPPED, OPTYPE DROP

OPTYPE RENAME exists and works, but classification depends on the exact SQL used. A RENAME old TO new; statement matches OPTYPE RENAME (GoldenGate logs OGG-10457 DDL RENAME found ... RENAME converted to ALTER TABLE while writing it, then classifies the resulting OGG-10451/OGG-10452 line as optype [RENAME]). But ALTER TABLE ... RENAME TO ... (the form most people write, since RENAME cannot be schema-qualified) matches OPTYPE ALTER instead, with no RENAME classification and no conversion message at all.

OBJNAME

Filters by object name. Supports wildcards (* for any sequence of characters, ? for a single character). For Oracle, use the schema.object format, or container.schema.object in multitenant. Write the value with no quotes at all. OBJNAME 'APP_SCHEMA.*' (single-quoted) abends with OGG-06377 Single quote marks cannot be used to delimit OBJNAME, and OBJNAME "APP_SCHEMA.*" (double-quoted) abends differently, with OGG-06585 No schema is specified. A double-quoted value is taken as one literal token and never splits on its dot into schema and object.

-- Only capture DDL for tables starting with ORD_ in APP_SCHEMA
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OBJNAME APP_SCHEMA.ORD_*

-- Exclude DDL on any staging table
DDL EXCLUDE MAPPED, OBJNAME APP_SCHEMA.*_STG

INSTR

Filters based on whether a literal string appears anywhere in the DDL text (case-sensitive). This is especially useful for excluding certain kinds of DDL.

-- Exclude any DDL containing NOLOGGING
DDL EXCLUDE MAPPED, INSTR 'NOLOGGING'

-- Exclude DDL referencing a specific tablespace by name
DDL EXCLUDE MAPPED, INSTR 'ARCHIVE_TBS'

INSTRWORDS

Similar to INSTR, but performs word-level matching. The order of the words does not matter, and partial matches within longer words are not counted.

-- Exclude DDL that contains both the words COMPRESS and OLTP (in any order)
DDL EXCLUDE MAPPED, INSTRWORDS 'COMPRESS OLTP'

EVENTACTIONS

Unlike the keywords above, EVENTACTIONS is not specific to DDL: it is a general GoldenGate keyword also usable on TABLE and MAP clauses, for triggering an action (REPORT, LOG, and others) whenever a record matches that clause. It happens to be usable inside a DDL clause too, comma-separated alongside OBJTYPE/OPTYPE/OBJNAME/INSTR like any other filter keyword, and it comes with real, DDL-specific behavior worth knowing.

EVENTACTIONS (REPORT) forces the same DDL replication statistics block that DDLOPTIONS REPORT writes on demand (see the DDLOPTIONS section below). However, it only works on an INCLUDE clause:

DDL INCLUDE MAPPED, EVENTACTIONS (REPORT)

2026-09-05 11:52:47  INFO    OGG-01487  DDL found, operation [create table ddltest.evt1 (id number) (size 37)], start SCN [24368602], commit SCN [24368634] instance [ (1)], DDL seqno [0], marker seqno [0].

2026-09-05 11:52:47  INFO    OGG-10451  DDL operation included [INCLUDE MAPPED, EVENTACTIONS (REPORT)], optype [CREATE], objtype [TABLE], catalog "PDB1", objowner "DDLTEST", objname "EVT1".

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************


Report at 2026-09-05 11:52:47.388472 (activity since 2026-09-05 11:50:36.261708)

Output to dt:



DDL replication statistics:

                    Operations:         0
             Mapped operations:         0
           Unmapped operations:         0
              Other operations:         0
           Excluded operations:         0


No records extracted.

This is verbose. A second CREATE TABLE matching the same clause produced a second, identical-shaped block right after its own OGG-10451 line. This means that every matching DDL gets its own full statistics dump on top of the usual OGG-01487/OGG-10451/OGG-10452 lines. On a replication with any real amount of DDL traffic, this multiplies report file growth well past what plain DDLOPTIONS REPORT alone produces. Try to restrict EVENTACTIONS (REPORT) to a narrow INCLUDE clause: a specific OBJTYPE, OPTYPE, or OBJNAME you want to log in the report file.

DDL INCLUDE MAPPED &
    EXCLUDE MAPPED, OPTYPE TRUNCATE, EVENTACTIONS (REPORT)

AND/OR Logic

Only one DDL parameter is allowed per parameter file. A parameter file with two separate DDL lines abends at startup with OGG-00403 There can be only one DDL filtering statement: “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.” Everything below happens inside that single DDL parameter: each clause after the first drops the leading DDL keyword and starts directly with INCLUDE or EXCLUDE, and the whole thing is continued with &.

AND: conditions within a single DDL clause

A DDL statement must satisfy all conditions to match.

-- Capture only CREATE TABLE in APP_SCHEMA (all three conditions must be true)
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE, OBJNAME APP_SCHEMA.*

A DROP TABLE APP_SCHEMA.ORDERS would not match this clause because OPTYPE DROP is different from OPTYPE CREATE.

OR: multiple INCLUDE/EXCLUDE clauses

Only one DDL parameter is allowed per parameter file, but that parameter can carry multiple INCLUDE/EXCLUDE clauses, separated with &. Multiple INCLUDE clauses are evaluated as a logical OR: a DDL statement is captured if it matches any INCLUDE clause. The indentation is optional.

-- Capture CREATE TABLE or CREATE SEQUENCE (either clause is sufficient)
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'SEQUENCE', OPTYPE CREATE

EXCLUDE always wins over INCLUDE

If a DDL statement matches both an INCLUDE clause and an EXCLUDE clause, the EXCLUDE wins. This lets you set broader INCLUDE rules and choose specific exceptions. An EXCLUDE clause always needs a corresponding INCLUDE clause in the same DDL parameter, it cannot stand alone.

-- Include all MAPPED DDL, but exclude TRUNCATE
DDL INCLUDE MAPPED &
    EXCLUDE MAPPED, OPTYPE TRUNCATE

A lone EXCLUDE clause with no corresponding INCLUDE still abends (OGG-06374 Invalid DDL statement: No INCLUDE). EXCLUDE narrows an INCLUDE; it cannot stand alone. Every multi-clause example in this post uses this single-DDL-parameter, &-continued form.

Filtering Examples

You can build nearly all sorts of DDL filtering in GoldenGate. But you should always ensure that they are relevant to the workload on the source and target databases !

Example 1: Structural DDL only, no destructive operations

Goal: replicate CREATE and ALTER on tables and sequences, but never DROP or TRUNCATE.

DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE ALTER &
    INCLUDE MAPPED, OBJTYPE 'SEQUENCE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'SEQUENCE', OPTYPE ALTER

Any DROP TABLE, TRUNCATE TABLE, or DROP SEQUENCE on a mapped object will be silently ignored.

Example 2: Everything except NOLOGGING directives

Here, the goal is to replicate all MAPPED DDL, but exclude any DDL containing the NOLOGGING directive.

DDL INCLUDE MAPPED &
    EXCLUDE MAPPED, INSTR 'NOLOGGING'

Example 3: Replicat applies only a subset of a rich trail

If a trail was generated with DDL INCLUDE ALL at the extract level, and you need the replicat to apply only CREATE TABLE and ALTER TABLE on mapped objects:

DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE ALTER
MAP APP_SCHEMA.*, TARGET TARGET_SCHEMA.*;

All other DDLs in the trail (DROP, TRUNCATE, index changes, sequences) are skipped by this replicat.

Example 4: Mapped objects plus a specific unmapped schema

-- Capture DDL for APP_SCHEMA (mapped) and also for SHARED_SCHEMA (not in the TABLE filtering)
DDL INCLUDE MAPPED &
    INCLUDE UNMAPPED, OBJNAME SHARED_SCHEMA.* &
    EXCLUDE MAPPED, OPTYPE TRUNCATE &
    EXCLUDE UNMAPPED, OPTYPE DROP
TABLE APP_SCHEMA.*;

DDLOPTIONS parameter

DDLOPTIONS often comes with DDL and controls what happens when DDL is processed. It is a global setting which applies to all DDL replication.

REPORT

Writes each DDL statement encountered (whether included or excluded) to the extract or replicat report file.

DDLOPTIONS REPORT

With DDL INCLUDE ALL, a CREATE TABLE DDLTEST.PROMOTIONS on the source produces entries like these in the report file:

2026-09-05 16:10:12  INFO    OGG-01487  DDL found, operation [create table ddltest.promotions (id number, discount number) (size 60)], start SCN [23026101], commit SCN [23026117] instance [ (1)], DDL seqno [0], marker seqno [0].
2026-09-05 16:10:12  INFO    OGG-10451  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], catalog "PDB1", objowner "DDLTEST", objname "PROMOTIONS".

This is very useful for testing, or when the DDL volume is low. However, never include DDLOPTIONS REPORT if you have a lot of DDLs. Your report files will grow without control.

A running process also keeps a running count of DDL statements by scope, separate from the per-statement DDLOPTIONS REPORT lines above. Request it on demand with SEND EXTRACT <name>, REPORT (or SEND REPLICAT <name>, REPORT) through the adminclient. It also gets written automatically whenever the process stops.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Report at 2026-09-05 11:34:23.315905 (activity since 2026-09-05 11:31:27.204191)

DDL replication statistics:

                    Operations:         1
             Mapped operations:         1
           Unmapped operations:         0
              Other operations:         0
           Excluded operations:         0

The report (Mapped/Unmapped/Other/Excluded) mirrors the scope model from the “Scopes” section above, so it is a quick way to check whether your filtering is doing what you expect without scrolling through every individual OGG-01487/OGG-10451/OGG-10452 line.

DDLERROR (Replicat only)

Controls what happens when a DDL statement fails on the target. The default is to abend.

DDLERROR DEFAULT DISCARD

DDLERROR is a top-level parameter, not a DDLOPTIONS sub-keyword. As you can imagine, use DISCARD with caution. It could silently leave the target schema out of sync.

Tag-Based DDL Filtering: EXCLUDETAG and INCLUDETAG

Oracle GoldenGate 26ai adds a second, independent filtering layer on top of everything above. You can now filter by tag rather than by scope or object. Every DDL statement GoldenGate encounters carries a tag identifying its origin.

By default, an extract applies DDLOPTIONS EXCLUDETAG +, which excludes all tagged DDL unless a tag is explicitly allowed with INCLUDETAG:

-- Exclude every tagged DDL statement by default (implicit default)
DDLOPTIONS EXCLUDETAG +

-- Explicitly allow DDL carrying tag 00 (Data Pump)
DDLOPTIONS INCLUDETAG 00

The practical impact is on tools that stamp a session tag on the DDL they issue. For instance, impdp tags its DDL with tag 00. Under GoldenGate 26ai default behavior, that DDL is excluded from replication unless the extract explicitly adds DDLOPTIONS INCLUDETAG 00. Please note that this is a behavioral change from earlier versions of GoldenGate. Before, impdp DDL was captured without any extra configuration needed. If an import via impdp is expected to replicate its DDL, check for this setting before assuming the default behavior still applies.

EXCLUDETAG/INCLUDETAG do not replace the scope and filter-keyword model. A DDL statement still has to pass the DDL INCLUDE/DDL EXCLUDE scope and keyword filtering described above. Both filtering systems have to allow the DDL.

Interaction with TABLE and MAP Parameters

Here are the scope (MAPPED/UNMAPPED/…) assignment rules for standard TABLE DDL.

Edge Cases

DDL errors from cascading object dependencies

Some DDL operations have cascading effects that GoldenGate does not replicate automatically.

DROP TABLE DDLTEST.CUSTOMERS CASCADE CONSTRAINTS;

2026-09-05 20:23:38  INFO    OGG-00482  DDL found, operation [drop table ddltest.customers cascade constraints].
2026-09-05 20:23:38  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [drop table "DDLTEST_TGT"."CUSTOMERS" cascade constraints].
2026-09-05 20:23:38  INFO    OGG-10451  DDL operation included [INCLUDE MAPPED], optype [DROP], objtype [TABLE], catalog "PDB1", objowner "DDLTEST_TGT", objname "CUSTOMERS".
2026-09-05 20:23:38  INFO    OGG-00484  Executing DDL operation.
2026-09-05 20:23:38  INFO    OGG-00483  DDL operation successful.

SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'DDLTEST_TGT' AND object_name = 'CUSTOMERS_V';

OBJECT_NAME    OBJECT_TYPE  STATUS
-------------- ------------ -------
CUSTOMERS_V    VIEW         INVALID

Complete Parameter File Examples

Here is an example of an extract/replicat pair, and how you could build complex DDL handling.

EXTRACT EXT
USERIDALIAS source_cdb DOMAIN OracleGoldenGate
EXTTRAIL pdb1/aa
SOURCECATALOG PDB1

-- Capture structural DDL on mapped objects, exclude storage directives,
-- and also capture DDL on a shared utility schema not in the TABLE list
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE ALTER &
    INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE RENAME &
    INCLUDE MAPPED, OBJTYPE 'SEQUENCE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'SEQUENCE', OPTYPE ALTER &
    EXCLUDE MAPPED, INSTR 'NOLOGGING' &
    EXCLUDE MAPPED, INSTR 'COMPRESS' &
    INCLUDE UNMAPPED, OBJNAME SHARED_SCHEMA.*, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE UNMAPPED, OBJNAME SHARED_SCHEMA.*, OBJTYPE 'TABLE', OPTYPE ALTER

-- Write DDL activity to the report file for auditing
DDLOPTIONS REPORT

TABLE APP_SCHEMA.*;
TABLE SHARED_SCHEMA.*;

REPLICAT REP
USERIDALIAS target_db DOMAIN OracleGoldenGate

-- Apply only CREATE and ALTER TABLE on mapped objects, plus shared-schema DDL;
-- skip sequences, drops, truncates, etc.
DDL INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE CREATE &
    INCLUDE MAPPED, OBJTYPE 'TABLE', OPTYPE ALTER &
    INCLUDE MAPPED, OBJNAME SHARED_SCHEMA.*

-- Write DDL activity to report file
DDLOPTIONS REPORT

-- Write discarded/failed DDL to discard file rather than abending
DDLERROR DEFAULT DISCARD

MAP APP_SCHEMA.*, TARGET TARGET_SCHEMA.*;
MAP SHARED_SCHEMA.*, TARGET SHARED_SCHEMA.*;

Summary

DDL filtering in GoldenGate is a layered system:

• Scope (MAPPED/UNMAPPED/OTHER/ALL) restricts DDL replication, based on your TABLE/MAP configuration
• Filter keywords (OBJTYPE, OPTYPE, OBJNAME, INSTR, INSTRWORDS) restricts DDL replication within the scope
• Only one DDL parameter is allowed per parameter file, but it can carry multiple INCLUDE/EXCLUDE clauses continued with &. AND logic applies within a single clause, while OR logic applies across clauses. EXCLUDE always wins over INCLUDE and always needs a corresponding INCLUDE
• DDLOPTIONS controls what happens when DDL is captured or replicated.
• Extract filtering is irreversible. You should filter at the replicat level when multiple replicats need different DDL settings.