CDC and Schema Changes

There is no tight coupling between the structure of CDC Change Tables and their associated Source Tables. If a change to the structure of a Source Table is made, the Change Table must also be updated or CDC can cause issues when trying to push data to the Change Table.

For example, during an application upgrade a field in a Source Table is increased in size. If the corresponding field in the Change Table is not also increased the first time data in that column exceeds the defined column size you will get an error.

Check that Captured Columns are in Source Tables
This step will help determine if a column has been dropped from a Source Table but is still referenced in a Capture Table.
If you are using ASYNC CDC and the Source and Change tables live on different servers you may need to run this query from the Source database and create a database link to access the information in the second part of the query.

SELECT source_table_name AS table_name,
column_name
FROM dba_published_columns
WHERE owner=&&change_owner.
MINUS
SELECT table_name, column_name
FROM dba_tab_columns
WHERE owner=&&source_owner.;

Look for column type discrepancies
This query checks for issues with mismatching column types mapped from the Source Table to the Change Table.
If you are using ASYNC CDC and the Source and Change tables live on different servers you may need to run this query from the Source database and create a database link to access the information in the second part of the query.

SELECT source_table_name AS table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_published_columns
WHERE owner=&&source_owner.
MINUS
SELECT table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_tab_columns
WHERE owner=&&change_owner.;