Removing Conflict Detection and Resolution From a Table

With Oracle Database 23c and higher, removing Automatic Conflict Detection and Resolution (ACDR) entirely from the table has lesser impact on the table because the ACDR related columns are marked as UNUSED.

Use the REMOVE_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package to tag a table as UNUSED, which minimizes blocking. You can choose to drop a column or retain it at a later stage.

See Removing a Column Group for examples.

Connect to the inbound server database as an Oracle GoldenGate administrator.
Run the REMOVE_AUTO_CDR procedure and specify the table.
Repeat all of the previous steps in each Oracle Database that replicates the table.

Example 8-15 Removing Conflict Detection and Resolution for a Table

This example removes conflict detection and resolution for the HR.EMPLOYEES table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR(
    SCHEMA_NAME => 'HR',
    TABLE_NAME  => 'EMPLOYEES');
END;
/

You can choose to drop columns by using the ADD_AUTO_CDR.REMOVE_HIDDEN_COLUMNS procedure to view and remove hidden columns.

Here is an example that you can use to view hidden columns in a table.

The following query uses the DBA_UNUSED_COL_TABS package to determine if there unused columns in the EMPLOYEES table.


SELECT OWNER, TABLE_NAME, COUNT
  FROM DBA_UNUSED_COL_TABS
  WHERE OWNER = 'HR'
  AND TABLE_NAME = 'EMPLOYEES'
  ORDER BY OWNER, TABLE_NAME;

The output displays as follows:

OWNER        TABLE_NAME    COUNT
--------    ------------ ----------
HR            EMPLOYEES      1

The following query lists out the hidden columns that were tagged by the system when ACDR was removed for the column group in the EMPLOYEES table.

SELECT OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
  FROM DBA_TAB_COLS
  WHERE OWNER = 'HR'
  AND TABLE_NAME = 'EMPLOYEES'
  AND HIDDEN_COLUMN = 'YES' AND USER_GENERATED= 'NO'
  ORDER BY OWNER, TABLE_NAME, COLUMN_ID;

The output displays as follows:

OWNER        TABLE_NAME    COLUMN_ID  COLUMN_NAME      DATA_TYPE     HIDDEN_COLUMN  
------------ ------------ ---------- -------------    ------------ ---------------
HR           EMPLOYEES    SYS_C00014_22092220:30:52$   TIMESTAMP(6)       YES