Re: Has anyone seen something like this?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Tue, 6 Apr 2010 09:34:20 -0700 (PDT)
Message-ID: <e39dc8f1-78b9-490c-815e-335ee0f97691_at_z4g2000yqa.googlegroups.com>

On Apr 6, 8:20 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 06 Apr 2010 09:01:13 -0700, Vladimir M. Zakharychev wrote:
> > On Apr 6, 6:33 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> >> The database is 10.2.0.4, linux x86-64. No RAC, just plain vanilla FS.
>
> >> SQL> select index_name,table_name from user_indexes
> >>   2  where status='UNUSABLE';
>
> >> INDEX_NAME                     TABLE_NAME
> >> ------------------------------ ------------------------------
> >> SEG_MIG_PUBLISHED              SEGMENT_MIG
>
> >> Elapsed: 00:00:00.12
> >> SQL> drop index SEG_MIG_PUBLISHED;
> >> drop index SEG_MIG_PUBLISHED
> >>            *
> >> ERROR at line 1:
> >> ORA-01418: specified index does not exist
>
> >> Trying to rebuild produces the same result. I opened a SR.
>
> >> --http://mgogala.byethost5.com
>
> > Please include INDEX_TYPE in your query. If this is a DOMAIN (Oracle
> > Text, for example) index, ORA-1418 might be thrown when index creation
> > crashed and index state can not be determined. If this is the case, try
> > DROP INDEX xxx FORCE. Should get rid of it.
>
> > Hth,
> >    Vladimir M. Zakharychev
> >    N-Networks, makers of Dynamic PSP(tm)http://www.dynamicpsp.com
>
> It's not a domain index. It's a function index. Tried with "force",
> doesn't do anything.
>
> --http://mgogala.byethost5.com

Can you post more details about this index? Something like this:

select index_name,
       index_type,
       table_name,
       table_type,
       partitioned,
       generated,
       secondary,
       ityp_owner,
       ityp_name,
       parameters,
       funcidx_status

  from user_indexes
 where status='UNUSABLE';

select column_expression,

       column_position
  from user_ind_expressions
 where index_name='......';

So far it looks like a data dictionary corruption, but the reason is unclear (and the solution therefore.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Apr 06 2010 - 11:34:20 CDT