Index does not exist but columns remain in user_ind_columns.

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 - 08:14:43 CST