Home » Other » General » specified index does not exist though it is exist
specified index does not exist though it is exist [message #201730] Mon, 06 November 2006 09:26 Go to next message
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
Re: specified index does not exist though it is exist [message #201772 is a reply to message #201730] Mon, 06 November 2006 12:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: specified index does not exist though it is exist [message #201800 is a reply to message #201730] Mon, 06 November 2006 16:31 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
What version of Oracle are you running?

David
Re: specified index does not exist though it is exist [message #201833 is a reply to message #201730] Tue, 07 November 2006 00:42 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Check metalink
i think it is bug.

hope this helps.
Mohammad Taj.
Re: specified index does not exist though it is exist [message #201937 is a reply to message #201800] Tue, 07 November 2006 08:29 Go to previous messageGo to next message
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
Re: specified index does not exist though it is exist [message #201938 is a reply to message #201833] Tue, 07 November 2006 08:32 Go to previous messageGo to next message
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
Re: specified index does not exist though it is exist [message #201944 is a reply to message #201937] Tue, 07 November 2006 08:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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.
Re: specified index does not exist though it is exist [message #482509 is a reply to message #201944] Fri, 12 November 2010 03:33 Go to previous messageGo to next message
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
Re: specified index does not exist though it is exist [message #482512 is a reply to message #482509] Fri, 12 November 2010 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use ANALYZE, use DBMS_STATS to gather statistics.

Regards
Michel
Re: specified index does not exist though it is exist [message #482514 is a reply to message #482509] Fri, 12 November 2010 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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
Re: specified index does not exist though it is exist [message #482526 is a reply to message #482514] Fri, 12 November 2010 04:18 Go to previous message
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



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


Current Time: Thu Mar 28 10:29:38 CDT 2024