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

This message occurs when an index (with a different name) exists, with the same column list. You need to search on column list not on index_name.

Appropriate primary key record to delete: you should never do that, you should drop the index.
I'm not sure whether you have already been doing similar things, it would explain why the datadictionary is inconsistent.

Hth,

--
Sybrand Bakker, Oracle DBA
Keith Jamieson <jamiesonk_at_phoenix.ie> wrote in message news:869pnc$hqq$1_at_kermit.esat.net...
> 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.
>
>
>
>
>
Received on Fri Jan 21 2000 - 09:38:20 CST