Re: Can't drop column
From: The Boss <nltaal_at_baasbovenbaas.demon.nl>
Date: Tue, 28 Jul 2009 10:36:08 -0700 (PDT)
Message-ID: <60fe7280-2322-4a95-9f2f-e7d9b465232c_at_k30g2000yqf.googlegroups.com>
On Jul 28, 7:14 pm, ddf <orat..._at_msn.com> wrote:
> On Jul 28, 12:24 pm, "Sicilian Dragon" <e4c5Nf..._at_sicilian.com> wrote:
>
> > 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.
>
> 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
Date: Tue, 28 Jul 2009 10:36:08 -0700 (PDT)
Message-ID: <60fe7280-2322-4a95-9f2f-e7d9b465232c_at_k30g2000yqf.googlegroups.com>
On Jul 28, 7:14 pm, ddf <orat..._at_msn.com> wrote:
> On Jul 28, 12:24 pm, "Sicilian Dragon" <e4c5Nf..._at_sicilian.com> wrote:
>
> > 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.
>
> 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
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!
-- JeroenReceived on Tue Jul 28 2009 - 12:36:08 CDT