Re: Cannot drop SYS_ constraints - Why?

hi Simon
Simon Goland wrote:
>
> For some reason (I guess multiple imports...) we now have many duplicate
> SYS_ constraints on many columns. So I wrote a combination of SQL/perl
> scripts to remove all duplicate SYS_ constraints for all columns in all
> tables. However, for some reason, these cannot be dropped (ORA-02443).
>
> Running the query for a specific user
>
> SELECT constraint_name,constraint_type,table_name
> FROM all_constraints
> WHERE table_name = 'PRODUCT'
> AND constraint_name like 'SYS%';
>
> gives me something like
>
> CONSTRAINT_NAME C TABLE_NAME
> ------------------------------ - ------------------------------
> SYS_C0044054 C PRODUCT
> SYS_C0044055 C PRODUCT
> SYS_C0044056 C PRODUCT
>
> (more output deleted... )
> (all constraints are ENABLED)
>
> And when I run the command (as the very same user)
>
> alter table PRODUCT drop constraint SYS_C0044056;
>
> I keep getting:
>
> alter table product drop constraint SYS_C0044056
> *
> ERROR at line 1:
> ORA-02443: Cannot drop constraint - nonexistent constraint
>
> What am I missing here?

        Use USER_CONSTRAINTS instead ALL_CONSTRAINTS in your query or add AND owner = user to WHERE clause

Regards

         Konstantin V. Sartakov
                Kuzbassprombank
                       Kemerovo
                         Russia
           mailto:skv_at_kpbank.ru