Re: Index does not exist but columns remain in user_ind_columns.

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 21 Jan 2000 18:24:44 +0800
Message-ID: <388833EC.1A7@yahoo.com>

Keith Jamieson wrote:
>
> Hi,
> I have a small problem. I am trying to create an index.
> When I create it, it falls over with the
> ORA-01408: such column list already indexed
> message.
>
> I looked for all the appropriate index name in the user_ind_columns
> view. When I try and delete the index, it tells me that no such index
> exists. Sure enough, if I look in the user_indexes view it is not there.
>
> I have now looked for the text upon which the vie wis based, which is pasted
> below.
>
> select idx.name, base.name,
> c.name, ic.pos#, c.length
> from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic
> where base.obj# = c.obj#
> and ic.bo# = base.obj#
> and ic.col# = c.col#
> and (base.owner# = userenv('SCHEMAID') or idx.owner# =
> userenv('SCHEMAID'))
> and ic.obj# = idx.obj#hjdjdjd
>
> My objective now is to get rid of the offending columns by identifying the
> appropriate primary key record to delete.
> I have two basic questions:
>
> i) What are the reasons that the database gets into this state.
> ii) Is there any safer method to tidy up the database.

Check ALL_INDEXES as well as USER_INDEXES - it may just be in a different schema.

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Fri Jan 21 2000 - 04:24:44 CST