Re: Cannot drop columns that once were part of primary key

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 03 Jan 2013 16:18:50 +1000
Message-ID: <50E522CA.6080001_at_tpg.com.au>

I do apologise - it's bad form to answer one's own questions.. :(

After some more googling I hit on the answer: this table had supplemental logging enabled, courtesy of Golden Gate. Once the GG supplemental logging group was dropped, the columns could be dropped without problem.

SQL> select log_group_name

    from dba_log_groups
   where table_name = 'DROP_COLS' ;

LOG_GROUP_NAME

---

GGS_DROP_COLS_01234 SQL> alter table drop_cols

    drop supplemental log group GGS_DROP_COLS_01234;

table altered

SQL> alter table drop_cols drop (operator_id, indicator, valid_ind);

table altered

Thanks,
Tony

On 3/01/13 3:54 PM, De DBA wrote:
> G'day.
>
> Version is 11.2.0.3.3. I am at a loss here. In an upgrade sequence a
> supplier-provided script drops a foreign key, then drops a primary key,
> recreates it minus 3 columns, recreates the foreign key and finally
> drops the 3 excluded columns.
>
> Dropping the 3 columns fails with ORA-12991: column referenced in
> multi-column constraint. I checked VPD policies, constraints, views,
> triggers: at that stage, there is absolutely no reference to any of the
> columns left!
>

...<snip>...

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 03 2013 - 07:18:50 CET