In an extract report, I noticed that the CREATE and DROP of a global temporary table were excluded (OGG-10473), while the TRUNCATE of the same table was included (OGG-00487). If the extract writes the TRUNCATE to the trail but not the CREATE, the replicat will try to apply it on a target where the table does not exist. The replicat abends with ORA-00942. Oracle support confirmed that this is the designed behavior for temporary tables, so the TRUNCATE has to be excluded or its error discarded in the replicat parameters. This post shows the failure, the affected DDL statements and ways to keep the replicat running.

Why global temporary table DDL is excluded

The data of a global temporary table is private to each session and is not replicated. The extract therefore skips the DDL that creates and drops it, and the table does not exist on the target. However, TRUNCATE operations are captured by GoldenGate.

Setup

  • GoldenGate 23.26.4.0.0 Microservices Architecture, deployment ogg_test_01.
  • Source: CDB01/PDB1, Oracle 19c. Target: CDB02/PDB2, Oracle 19c.
  • A GTTTEST schema exists in both PDBs.
  • Integrated extract EGTT connects to PDB1 directly (PDB-level capture), with DDL INCLUDE ALL.
  • Integrated replicat RGTT applies to PDB2, with DDL INCLUDE ALL.

The parameter file of the extract:

EXTRACT EGTT
EXTTRAIL gt
USERIDALIAS source_pdb1 DOMAIN OracleGoldenGate
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE GTTTEST.*;

The parameter file of the replicat:

REPLICAT RGTT
USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP GTTTEST.*, TARGET GTTTEST.*;

The extract is registered with REGISTER EXTRACT EGTT DATABASE, and the replicat uses the checkpoint table OGGADMIN.CHECKPOINT.

Workload on the source

The three statements run as GTTTEST on PDB1:

SQL> CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS;

Table created.

SQL> TRUNCATE TABLE temp_test;

Table truncated.

SQL> DROP TABLE temp_test PURGE;

Table dropped.

What the extract does

The extract report shows the three DDL statements:

  • CREATE and DROP are excluded.
  • TRUNCATE is included.
2026-10-24 07:26:05  INFO    OGG-01487  DDL found, operation [CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS (size 74)], ... ].
2026-10-24 07:26:05  INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
2026-10-24 07:26:05  INFO    OGG-01487  DDL found, operation [TRUNCATE TABLE temp_test (size 24)], ... ].
2026-10-24 07:26:05  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
2026-10-24 07:26:05  INFO    OGG-01487  DDL found, operation [DROP TABLE temp_test PURGE (size 26)], ... ].
2026-10-24 07:26:05  INFO    OGG-10473  DDL on global temporary table excluded, optype [DROP], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".

Only the TRUNCATE reaches the trail.

What the replicat does

The replicat maps the TRUNCATE to GTTTEST.TEMP_TEST and executes it on PDB2. The table does not exist there, and the replicat abends:

2026-10-24 07:26:08  INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
2026-10-24 07:26:08  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [TRUNCATE TABLE "GTTTEST"."TEMP_TEST" (size 36)].
2026-10-24 07:26:08  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
2026-10-24 07:26:08  INFO    OGG-00484  Executing DDL operation.
2026-10-24 07:26:08  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist
2026-10-24 07:26:08  ERROR   OGG-30538  Fatal error executing DDL statement [TRUNCATE TABLE "GTTTEST"."TEMP_TEST"].
2026-10-24 07:26:08  ERROR   OGG-00664  Error 942 on OCI call [ORA] - ORA-00942: table or view does not exist
2026-10-24 07:26:13  ERROR   OGG-01668  PROCESS ABENDING.

The status in the adminclient:

OGG (http://localhost:7810 ogg_test_01) 2> INFO ALL

Program     Status      Group       Type                   Lag at Chkpt       Time Since Chkpt
EXTRACT     RUNNING     EGTT        INTEGRATED                  00:00:03           00:00:04
REPLICAT    ABENDED     RGTT        INTEGRATED                  00:00:04           00:00:13

DDLOPTIONS for global temporary tables

The DDLOPTIONS documentation describes two options for global temporary tables. By default, GTT DDL is not visible to the extract, which does not match the TRUNCATE captured above.

  • CAPTUREGLOBALTEMPTABLE (extract): makes GTT DDL visible to the extract. It is filtered like any other DDL and, if it passes, written to the trail.
  • NOAPPLYGLOBALTEMPTABLE (replicat): prevents the replicat from applying GTT DDL. Without it, the replicat applies all GTT DDL from the trail.

Every case uses the same workload. It has the three GTT statements, then a regular table with one row going through CREATE, TRUNCATE and DROP. Each case starts from a new extract, replicat and trail.

NOAPPLYGLOBALTEMPTABLE on the replicat

The parameter file of the replicat:

REPLICAT RGTT
USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
DDL INCLUDE ALL
DDLOPTIONS REPORT, NOAPPLYGLOBALTEMPTABLE
MAP GTTTEST.*, TARGET GTTTEST.*;

The replicat still abends on the TRUNCATE, with the same errors as without the option:

INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist
ERROR   OGG-01668  PROCESS ABENDING.

If the GTT already exists on the target, the replicat does not abend, because the TRUNCATE is applied to that table.

CAPTUREGLOBALTEMPTABLE on the extract

With DDLOPTIONS REPORT, CAPTUREGLOBALTEMPTABLE in the extract, the CREATE and DROP are no longer excluded:

OGG-00487  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
OGG-00487  DDL operation included [INCLUDE ALL], optype [DROP], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".

The replicat creates the table on the target, truncates it and drops it. It keeps running:

INFO    OGG-00482  DDL found, operation [CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS (size 74)].
INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [CREATE GLOBAL TEMPORARY TABLE "GTTTEST"."TEMP_TEST" (c number) ON COMMIT PRESERVE ROWS (size 86)].
INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
INFO    OGG-00482  DDL found, operation [DROP TABLE temp_test PURGE (size 26)].

Both options together

With CAPTUREGLOBALTEMPTABLE on the extract and NOAPPLYGLOBALTEMPTABLE on the replicat, the replicat excludes the CREATE and still executes the TRUNCATE:

INFO    OGG-00482  DDL found, operation [CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS (size 74)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist
ERROR   OGG-01668  PROCESS ABENDING.

The replicat abends on the TRUNCATE before it reaches the DROP. To check the DROP, the same two options run with a workload that has no TRUNCATE on the global temporary table. The replicat excludes both the CREATE and the DROP and keeps running:

INFO    OGG-00482  DDL found, operation [CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS (size 74)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00482  DDL found, operation [DROP TABLE temp_test PURGE (size 26)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [DROP], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".

So NOAPPLYGLOBALTEMPTABLE skips the CREATE and DROP of a global temporary table, but not its TRUNCATE.

Which DDL statements are affected

The TRUNCATE is not the only statement that behaves differently from CREATE and DROP. This workload runs on the source with the default extract (no CAPTUREGLOBALTEMPTABLE):

CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS;
ALTER TABLE temp_test ADD (d number);
COMMENT ON TABLE temp_test IS 'gtt';
CREATE INDEX temp_test_i ON temp_test(c);
TRUNCATE TABLE temp_test;
ALTER TABLE temp_test RENAME TO temp_test2;
DROP TABLE temp_test2 PURGE;

The extract excludes five of the seven statements and includes COMMENT and TRUNCATE:

INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-10473  DDL on global temporary table excluded, optype [ALTER], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [COMMENT], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [INDEX], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-10473  DDL on global temporary table excluded, optype [ALTER], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST2".
INFO    OGG-10473  DDL on global temporary table excluded, optype [DROP], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST2".

With CAPTUREGLOBALTEMPTABLE on the extract and NOAPPLYGLOBALTEMPTABLE on the replicat, the extract captures every statement and the replicat makes the same decision. It excludes CREATE and ALTER (OGG-10473), then applies the COMMENT and abends:

INFO    OGG-00482  DDL found, operation [ALTER TABLE temp_test ADD (d number) (size 36)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [ALTER], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00482  DDL found, operation [COMMENT ON TABLE temp_test IS 'gtt' (size 35)].
INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [COMMENT], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist
ERROR   OGG-30538  Fatal error executing DDL statement [COMMENT ON TABLE "GTTTEST"."TEMP_TEST" IS 'gtt'].
ERROR   OGG-01668  PROCESS ABENDING.

Each statement runs separately to see what the replicat does with it:

Statement on the global temporary tableReplicat
CREATE TABLE, DROP TABLEExcluded (OGG-10473)
ALTER TABLE ... ADD, ALTER TABLE ... RENAME TOExcluded (OGG-10473)
CREATE INDEX, GRANTExcluded (OGG-10473)
TRUNCATE TABLEApplied, abends with ORA-00942
COMMENT ON TABLE, COMMENT ON COLUMNApplied, abends with ORA-00942

Scope and truncate options do not change it

A difference in DDL scope could explain why CREATE is excluded and TRUNCATE is not. However, with DDL INCLUDE MAPPED on the replicat, the TRUNCATE is still included and the replicat abends:

INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
INFO    OGG-00487  DDL operation included [INCLUDE MAPPED], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist
ERROR   OGG-30538  Fatal error executing DDL statement [TRUNCATE TABLE "GTTTEST"."TEMP_TEST"].
ERROR   OGG-01668  PROCESS ABENDING.

The same happens with GETTRUNCATES and with IGNORETRUNCATES in the replicat, and with a global temporary table created with ON COMMIT DELETE ROWS. Those parameters control the truncate operation of the data, not the TRUNCATE DDL statement.

Answer from Oracle Support

I opened a service request with Oracle. The Product Team answered that TRUNCATE TABLE is a DDL operation and has to be excluded with an EXCLUDE clause of the DDL parameter. DDL cannot be filtered by temporary versus permanent tables, so the exclusion is done by table name. If that is too much work, the alternative is DDLERROR 942 DISCARD. I then pointed out that CREATE and DROP are DDL operations too and are excluded. The answer was that this is how it is designed for temporary tables.

Keeping the replicat running

Exclude the TRUNCATE by table name

The DDL parameter of the replicat excludes the TRUNCATE of the table:

REPLICAT RGTT
USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
DDL INCLUDE ALL, EXCLUDE OBJNAME GTTTEST.TEMP_TEST OPTYPE TRUNCATE
DDLOPTIONS REPORT, NOAPPLYGLOBALTEMPTABLE
MAP GTTTEST.*, TARGET GTTTEST.*;

The replicat finds the TRUNCATE and skips it with an informational message, OGG-00488. It keeps running:

INFO    OGG-00482  DDL found, operation [CREATE GLOBAL TEMPORARY TABLE temp_test (c number) ON COMMIT PRESERVE ROWS (size 74)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [CREATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [TRUNCATE TABLE "GTTTEST"."TEMP_TEST" (size 36)].
INFO    OGG-00488  DDL operation excluded [EXCLUDE OBJNAME GTTTEST.TEMP_TEST OPTYPE TRUNCATE], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00482  DDL found, operation [DROP TABLE temp_test PURGE (size 26)].
INFO    OGG-10473  DDL on global temporary table excluded, optype [DROP], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".

The exclusion works per table name, so it has to list every global temporary table. COMMENT ON TABLE and COMMENT ON COLUMN abend the same way, so the exclusion must cover them too.

Discard the ORA-00942 error

DDLERROR 942 DISCARD needs no table list. The replicat applies the TRUNCATE, receives ORA-00942 from the target and discards the error:

REPLICAT RGTT
USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
DISCARDFILE RGTT.dsc, APPEND
DDL INCLUDE ALL
DDLOPTIONS REPORT
DDLERROR 942 DISCARD
MAP GTTTEST.*, TARGET GTTTEST.*;

The report shows OGG-00494 instead of the abend:

INFO    OGG-00482  DDL found, operation [TRUNCATE TABLE temp_test (size 24)].
INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], objowner "GTTTEST", objname "TEMP_TEST".
INFO    OGG-00494  DDL error discarded: error code [942], filter [include all (default)], error text [Current time: 2026-10-24 07:55:57
 Error text [Error code [942], ORA-00942: table or view does not exist
OGG (http://localhost:7810 ogg_test_01) 2> INFO ALL

Program     Status      Group       Type                   Lag at Chkpt       Time Since Chkpt
EXTRACT     RUNNING     EGTT        INTEGRATED                  00:00:03           00:00:07
REPLICAT    RUNNING     RGTT        INTEGRATED                  00:00:00           00:00:06

The discard file RGTT.dsc has the statement:

Current time: 2026-10-24 07:55:57
 Error text [Error code [942], ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
], operation [TRUNCATE TABLE "GTTTEST"."TEMP_TEST" (size 36)]
Operation failed at seqno 0 rba 1,333.

The parameter applies to every DDL statement that fails with ORA-00942, not only to temporary tables. A statement that fails because a regular table is missing on the target is discarded too, so check the discard file regularly.

Summary

  • With DDL INCLUDE ALL, the extract excludes CREATE, ALTER, CREATE INDEX and DROP of a global temporary table (OGG-10473) but includes TRUNCATE and COMMENT (OGG-00487).
  • The TRUNCATE is written to the trail and applied by the replicat on a target that has no such table.
  • The replicat abends with ORA-00942 (OGG-00519, OGG-30538, OGG-01668).
  • DDL INCLUDE MAPPED, GETTRUNCATES and IGNORETRUNCATES do not change the abend.
  • NOAPPLYGLOBALTEMPTABLE on the replicat does not prevent it, because the TRUNCATE is still executed.
  • CAPTUREGLOBALTEMPTABLE on the extract makes the whole CREATE, TRUNCATE and DROP sequence replicate without error.
  • Oracle support confirmed that this is the designed behavior for temporary tables.
  • DDL EXCLUDE OBJNAME <table> OPTYPE TRUNCATE on the replicat skips the TRUNCATE for the listed tables.
  • DDLERROR 942 DISCARD on the replicat keeps it running for all tables and writes the failed statement to the discard file.
Views: 1
Thumbnail [60x60]
by
Julien Delattre