DELETE TRANDATA

Use DELETE TRANDATA to do one of the following:

Db2 LUW and Db2 z/OS: Alters the table to DATA CAPTURE NONE.
Oracle: Disable supplemental logging.
SQL Server: Stops extended logging for a table.
PostgreSQL: Alters the table’s REPLICA IDENTITY to NOTHING.

By default, this command attempts to remove the supplemental logging of the key columns that are used by Oracle GoldenGate (can be the primary key, a unique key, KEYCOLS columns, or all columns) and also the scheduling columns. The scheduling columns are the primary key, all of the unique keys, and all of the foreign keys. To delete the logging of the Oracle GoldenGate key columns, but not the scheduling columns, include the NOSCHEDULINGCOLS option with DELETE TRANDATA. If ADD TRANDATA was issued with the ALLCOLS option, use DELETE TRANDATA with the ALLCOLS option to remove the supplemental logging of all of the columns, including the Oracle GoldenGate key columns. DELETE TRANDATA will disable logical replication for a table after all the table level supplemental logging has been disabled or removed. This behavior is supported from Oracle 19c and higher. Also, if a the DELETE TRANDATA operation removes the last supplemental log group on a table then it will also perform an ALTER TABLE owner.table DISABLE LOGICAL REPLICATION too.

It is mandatory to run ADD TRANDATA table_name command to enable logical replication after running

DELETE
                    TRANDATA

.

Note:

You cannot enable logical replication using

ADD
                    SCHEMATRANDATA

.

Use the DBLOGIN command to establish a database connection before using this command. The user specified with this command must have the same privileges that are required for ADD TRANDATA.

Syntax

DELETE TRANDATA [container.]owner.table [NOSCHEDULINGCOLS | ALLCOLS]

[container.]owner.table: The pluggable database (if this is an Oracle multitenant container database), owner and name of the table or file. A wildcard can be used for any name component.
NOSCHEDULINGCOLS: Prevents the command from removing the supplemental logging of the scheduling columns of the specified table. The scheduling columns are the primary key, all of the unique keys, and all of the foreign keys of a table.
ALLCOLS: Removes the supplemental logging of all of the columns of the specified table.

Examples

DELETE TRANDATA hr.employees

DELETE TRANDATA hr.reg*

DELETE TRANDATA hr.jobs ALLCOLS