SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

Question

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is:

SQL Error: ORA-12991: column is reference in a multi-column constraint Cause: an attempt was made to drop a column referenced by some constraints Action: Drop all constraints referencing the dropped column or specify CASCADE CONSTRAINTS in statement.

So i have gone ahead and searched for all the constraints that referenced the column by doing something like:

SELECT * FROM USER_TAB_COLS WHERE COLUMN_NAME = 'My_Column_Name'

After finding and removing all the referenced constraints on the column that i want to drop I tried to drop the column again but i am still getting the same issue. Before posting the question i did take some time to research before this and have read the potential of this being a "Supplemental Logging" issue. Again i am very new to oracle DB programming as such could someone please shed some light on this and maybe suggest how and why i can not drop the column after having temporarily removed the constraints that reference the column i want to drop? Thanks in advance!

PS: Using Cascade Constraint Personally i don't think will fix the issue and i also do not want to remove all constraints without knowing which of them are being removed. So this is not a viable solution for me.

Answer 1

Setup:

create table t3 (c1 number, c2 number);
alter table t3 add  supplemental log group sl1 (c1, c2) always;
alter table t3 add constraint t3_uk unique (c1, c2);
insert into t3 values (1, 2);
commit;

Try to drop the column:

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

So far so good, goes as expected. Drop the constraint:

SQL> alter table t3 drop constraint t3_uk;

Table altered.

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Still doesn't work, same error. Check the supplemental log group:

select * from user_log_groups where table_name = 'T3'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE      ALWAYS      GENERATED
----- -------------- ---------- ------------------- ----------- --------------
BP    SL1            T3         USER LOG GROUP      ALWAYS      USER NAME

Drop the supplemental log group, then try dropping the column again:

SQL> alter table t3 drop supplemental log group sl1;

Table altered.

SQL> alter table t3 drop column c2;

Table altered.

Answer 2

If your application is having GG replication. Ensure GG is stopped, delete the trandata. Execute the drop and then add trandata. Start GG.

These are the steps followed by my DBA.