Can't drop column

[Sicilian Dragon]

we're running this statement from a VB program

ALTER TABLE MyTable DROP COLUMN MyCol

However I get an error.

ORA-00604: error occured at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_COL_USAGE$) violated

No indexes/constraints have been created on this column (MyCol)
I've searched this error on google and it seems many people have been
troubled by this however nowhere did I find a satisfactory answer.

[NetComrade]

On Tue, 28 Jul 2009 22:24:12 +0500, "Sicilian Dragon"
<e4c5...@sicilian.com> wrote:

>ALTER TABLE MyTable DROP COLUMN MyCol
>
>
>However I get an error.
>
>ORA-00604: error occured at recursive SQL level 1
>ORA-00001: unique constraint (SYS.I_COL_USAGE$) violated

Have you checked if if the column is being referenced by other tables?
.......
We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc)
We use RMAN and remote catalog for backups

[ddf]

Provide a create table statement for MyTable so someone can test
this. Also provide the Oracle release you're using (all four or five
numbers) so the problem can be replicated as accurately as possible.

This appears to be a constraint on a data dictionary table, not one on
MyTable. You should have the data dictionary checked for invalid
objects.

David Fitzjarrell

[The Boss]

I_COL_USAGE$ is an index on table COL_USAGE$ that is used in a script
for the upgrade from 817 to 9i, see:
http://forums.oracle.com/forums/thread.jspa?threadID=70282
OP may be hit by bug 2144889 as described on Metalink DocID:171803.1
He might try to complete the drop operation via
ALTER TABLE DROP COLUMNS CONTINUE
If that fails, he should do what is the recommended ACTION for message
ORA-00604:
"If the situation described in the next error on the stack can be
corrected, do so; otherwise contact Oracle Support."

HTH.

Cheers!

--
Jeroen