Database Software

Hi,

Is there a way to get rid of an invalid check constraint after attempting to add it to a Workspace Manager enabled table?

If I do the following on Oracle 10.2.0.1:

create table table1 ( id number(20) not null, data varchar2(50), primary key (id));
exec dbms_wm.enableversioning('TABLE1');
insert into table1 values ( 1, 'a' );
exec dbms_wm.beginddl('TABLE1');
alter table table1_lts add check ( id > 1);
exec dbms_wm.commitddl('TABLE1');

I get "cannot validate (BR.SYS_C00580318) - check constraint violated", which is expected. However, at this point the table1_lts is gone so the ddl operation cannot be rolled back. If I retry the commit, ignoring errors:

exec dbms_wm.commitddl('TABLE1',TRUE);

the ddl operation completes, but the check constraint is still present in user_wm_constraints. If I then do the following:

exec dbms_wm.beginddl('TABLE1');
exec dbms_wm.commitddl('TABLE1');

I get "cannot validate (BR.SYS_C00580318_LTS) - check constraint violated" so it appears to retry adding the invalid constraint. I've also tried dropping the constraint again during the second ddl operation. This prevents the second error but the constraint still hangs around in user_wm_constraints.

One thing I might be missing is some sort of cleanup step, after the first commitddl, that would get rid of the constraint completely. Is there some sort of extra operation I should be doing at this point?