Re: Has anyone seen something like this?

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 7 Apr 2010 07:36:43 -0700 (PDT)
Message-ID: <76f44ca1-ce7c-4f35-a608-d31a1270190f_at_k13g2000yqe.googlegroups.com>

On Apr 7, 9:54 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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

Nos sure if it's relevant, but indexes 'DESC' (descending) are implemented as FBI's.:

Create unique index T1_PK_IDX on TABLE1(id_n DESC) ;

ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (id_n) USING INDEX T1_PK_IDX; -->> "ORA-01418: specified index does not exist" !!

HTH Cheers.

Carlos. Received on Wed Apr 07 2010 - 09:36:43 CDT