Removing Conflict Detection and Resolution From a Table

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

After calling the REMOVE_AUTO_CDR procedure, the unused columns can be manually deleted in a maintenance window. This is useful for large tables where the ALTER TABLE ... DROP COLUMN operation is resource intensive.

If you want to remove all AUTO_CDR internal columns immediately when calling the REMOVE_AUTO_CDR procedure, you have to first mark the table using the additional_options parameter REMOVE_HIDDEN_COLUMNS for the ADD_AUTO_CDR or ALTER_AUTO_CDR procedure.

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.

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 9-28 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 flag as an additional_flags parameter in the ADD_AUTO_CDR procedure.

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