Re: Has anyone seen something like this?

From: Mladen Gogala <no_at_email.here.invalid>
Date: Tue, 6 Apr 2010 20:33:31 +0000 (UTC)
Message-ID: <pan.2010.04.06.20.33.30_at_email.here.invalid>

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

Received on Tue Apr 06 2010 - 15:33:31 CDT