Re: Has anyone seen something like this?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 7 Apr 2010 00:54:05 -0700 (PDT)
Message-ID: <7dcf9d9a-9d8a-4a7d-8ebf-472a6803cd48_at_i37g2000yqn.googlegroups.com>

On Apr 7, 12:33 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 06 Apr 2010 09:34:20 -0700, Vladimir M. Zakharychev wrote:
> > 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';
>
> INDEX_NAME                     INDEX_TYPE                  
> TABLE_NAME                TABLE_TYPE  PAR G S ITYP_OWNER
> ------------------------------ ---------------------------
> ------------------------------ ----------- --- - -
> ------------------------------
> ITYP_NAME
> ------------------------------
> PARAMETERS
> ------------------------------------------------------------------------------------------------------------------------------------------------------
> FUNCIDX_
> --------
> SEG_MIG_PUBLISHED              FUNCTION-BASED NORMAL      
> SEGMENT_MIG               TABLE       NO  N N
>
>
>
> > select column_expression,
> >        column_position
> >   from user_ind_expressions
> >  where index_name='......';
>
> SQL> select column_expression,column_position
>   2  from user_ind_expressions
>   3  where index_name='SEG_MIG_PUBLISHED';
>
> no rows selected
>
> Elapsed: 00:00:00.32
>
>
>
> > So far it looks like a data dictionary corruption, but the reason is
> > unclear (and the solution therefore.)
>
> I know. That is why I opened a SR.
>
> --http://mgogala.byethost5.com

Huh... A function-based index without expression (function)? And did I read correctly that FUNCIDX_STATUS is NULL? This must be the reason why it can't be dropped. How did you create it? Did the index creation fail?

Internally, FBIs are created by adding a hidden generated column to the base table with default value of the function expression and then indexing that column. In this case it seems that the column was not added for some reason. Can you confirm that SYS_NCnnnnn$ column for the FBI is not present by querying USER_TAB_COLS WHERE TABLE_NAME='SEGMENT_MIG' AND HIDDEN='YES'? If it's there, DATA_DEFAULT will have the expression - but in this case there must be corresponding row in USER_IND_EXPRESSIONS and there are none... And does the index have a segment (that is, was it physically created?)

And, as dumb as it may sound, can it be that index name contains spaces or other unprintable characters? Like, "SEG_MIG_PUBLISHED ". Might be the reason you can't drop it and you don't see anything in USER_IND_EXPRESSIONS. Any progress with the SR by the way?

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Apr 07 2010 - 02:54:05 CDT