OraFAQ Forum: General » specified index does not exist though it is exist

Home » Other » General » specified index does not exist though it is exist

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

specified index does not exist though it is exist [message #201730]

Mon, 06 November 2006 09:26

litalh
Messages: 4
Registered: November 2006

Junior Member

Hi all,

This problem occures only sometimes. I'm getting the list of indexes from the user_indexes table and later perform on them a rebuild operation by using the command :
Alter index index_name coalesce

This might cause on some indexes to the following error:
ORA-01418: specified index does not exist

I know these indexes exist and I didn't find any reson for this situation.

Does anyone have any idea about it ?

Thanks,
Lital

Report message to a moderator

Re: specified index does not exist though it is exist [message #201772 is a reply to message #201730]

Mon, 06 November 2006 12:21

Littlefoot
Messages: 21736
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you post output of

SELECT * FROM user_indexes WHERE index_name = 'THAT_PARTICULAR_INDEX';

so that we could see how it looks like. Not that it *surely* will help, but ... could be a starting point.

Report message to a moderator

Re: specified index does not exist though it is exist [message #201800 is a reply to message #201730]

Mon, 06 November 2006 16:31

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

What version of Oracle are you running?

David

Report message to a moderator

Re: specified index does not exist though it is exist [message #201833 is a reply to message #201730]

Tue, 07 November 2006 00:42

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Check metalink
i think it is bug.

hope this helps.
Mohammad Taj.

Report message to a moderator

Re: specified index does not exist though it is exist [message #201937 is a reply to message #201800]

Tue, 07 November 2006 08:29

litalh
Messages: 4
Registered: November 2006

Junior Member

Hi,

I'm running an enterprise edition of version 10.2.0.1.0

hope this helps

Report message to a moderator

Re: specified index does not exist though it is exist [message #201938 is a reply to message #201833]

Tue, 07 November 2006 08:32

litalh
Messages: 4
Registered: November 2006

Junior Member

Hi,

I've searched the metalink but couldn't find a bug that matches might be connected to it.
Do you have any information about this bug ?

Thanks a lot,
Lital

Report message to a moderator

Re: specified index does not exist though it is exist [message #201944 is a reply to message #201937]

Tue, 07 November 2006 08:40

Mahesh Rajendran
Messages: 10704
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You have not yet answered Littlefoot's question.

Quote:

Could you post output of
SELECT * FROM user_indexes WHERE index_name = 'THAT_PARTICULAR_INDEX';

You might have created the index with double quotes.

Report message to a moderator

Re: specified index does not exist though it is exist [message #482509 is a reply to message #201944]

Fri, 12 November 2010 03:33

oravijay
Messages: 43
Registered: March 2010

Member

Hi Oracle Experts,

Please let me know the reason

SQL> sho user
USER is "RAHS2"
SQL> select index_name,table_name from user_indexes where index_name='IX_objcontent2_rating';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IX_objcontent2_rating OBJCONTENT2

SQL> analyze index IX_objcontent2_rating compute statistics;
analyze index IX_objcontent2_rating compute statistics
*
ERROR at line 1:
ORA-01418: specified index does not exist

SQL> analyze index rahs2.IX_objcontent2_rating compute statistics;
analyze index rahs2.IX_objcontent2_rating compute statistics
*
ERROR at line 1:
ORA-01418: specified index does not exist

Thanks in Advance
Vijay

Report message to a moderator

Re: specified index does not exist though it is exist [message #482512 is a reply to message #482509]

Fri, 12 November 2010 03:40

Michel Cadot
Messages: 68259
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do not use ANALYZE, use DBMS_STATS to gather statistics.

Regards
Michel

Report message to a moderator

Re: specified index does not exist though it is exist [message #482514 is a reply to message #482509]

Fri, 12 November 2010 03:47

cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well since the index_name isn't in upper case in user_indexes it's safe to assume the index was created with it's name wrapped in double quotes.
This means that it's case sensitive and you have to wrap it's name in double quotes each time to want to refer to it in code.
Here's the same thing with a table:

SQL> create table "bob" (a number);

Table created.

SQL> select * from bob;
select * from bob
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "bob";

no rows selected

SQL>

Suggest you drop the index and re-create it without the double-quotes

Report message to a moderator

Re: specified index does not exist though it is exist [message #482526 is a reply to message #482514]

Fri, 12 November 2010 04:18

oravijay
Messages: 43
Registered: March 2010

Member

Great
Thanks for all

>>>"Suggest you drop the index and re-create it without the double-quotes"

This solved my Issue

Thanks & Regards
Vijay

Report message to a moderator

Previous Topic:	URGENT,email notification
Next Topic:	Tool for executing oracle scripts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 15 13:20:38 CDT 2022