:

ORA-31403: change table already contains a column

Imagine the scenario: You have a change set and your Change Data Capture (CDC) configuration has been running smoothly for a few weeks now. Your boss tells you that there is an upgrade planned soon and that the provisional changes included adding columns to the source tables that you have based your change tables on. No problem, you’ll use the DBMS_CDC_PUBLISH package to add an additional column to the change table and it will all be working again within no time.

So, you start work and test out a script against your change table and get the following error:

begin
dbms_cdc_publish.alter_change_table(
owner => ‘CDC_DWSDE_PUB’,
change_table_name => ‘TEST_ADDCHANGESET_CT’,
operation => ‘ADD’,
column_list => ‘INITIALS VARCHAR2(5)’,
rs_id => ‘n’,
row_id => ‘n’,
user_id => ‘n’,
timestamp => ‘y’,
object_id => ‘n’,
source_colmap => ‘n’,
target_colmap => ‘n’);
end;
/
begin
*
ERROR at line 1:
ORA-31403: change table  already contains a column
ORA-06512: at “SYS.DBMS_CDC_PUBLISH”, line 633
ORA-06512: at line 2

This is not what you were expecting! You check through and confirm that all of the details are correct and a column with that name definitely does not already exist. It doesn’t work you say! I’ve got it all right and it doesn’t work! Well, kind of…

What does this ORA-31403 error mean? This error is because when the change table was originally created the values for rs_id, row_id, user_id, timestamp, object_id, source_colmap and target_colmap were already specified. In order to prevent this error change all the values for the aforementioned columns to ‘n’ and it should run through without error.

Like it, share it...

Category: Change Data Capture


Related Posts

Comments (2)

Trackback URL | Comments RSS Feed

  1. fprovot says:

    You saved my day!! Thanks, that error message is quiet a bug, and I wouldn’t have though of setting parameters to N!! I don’t know when you posted that, but thanks again.

    • Hello, glad that it helped you out. I wrote it a while ago but some of these errors can be around for years…Hope it is all working well for you now.
      Cheers,
      Rob

Leave a Reply

Your email address will not be published. Required fields are marked *