Home » RDBMS Server » Server Administration » Index problem (Oracle 11.2.0.1, OS Windows 2008 R2)
Index problem [message #608621] Fri, 21 February 2014 07:42 Go to next message
patdev
Messages: 73
Registered: August 2008
Member
Hi,

I am trying to create index schemauser.index name_A_IDX2 on schemaname.table

but it gives me following error
ORA-00955: name is already used by an existing object

but when i try to drop the index by
drop index schemaname.indexname it says
ORA-01418: specified index does not exist

WHY!!
please help!!
Re: Index problem [message #608622 is a reply to message #608621] Fri, 21 February 2014 07:44 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Please use SQL*Plus to repeat what you have told us. Then copy & paste here the output of the whole session so we can help you.
Re: Index problem [message #608623 is a reply to message #608622] Fri, 21 February 2014 07:49 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
SQL> connect user/pw@DB
Connected.
SQL> drop index indiex_A_IDX2;
drop index index_A_IDX2
*
ERROR at line 1:
ORA-01418: specified index does not exist


SQL>

Re: Index problem [message #608624 is a reply to message #608623] Fri, 21 February 2014 07:50 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
and then if it try to create with create index script it says:
ORA-00955: name is already used by an existing object
Re: Index problem [message #608625 is a reply to message #608624] Fri, 21 February 2014 07:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It would help if you don't fake what you are doing. Hint: INDIEX vs. INDEX.
Re: Index problem [message #608626 is a reply to message #608624] Fri, 21 February 2014 07:53 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
select object_type, owner
from dba_objects 
where object_name = 'INDIEX_A_IDX2'

Re: Index problem [message #608627 is a reply to message #608625] Fri, 21 February 2014 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Fri, 21 February 2014 13:53
It would help if you don't fake what you are doing. Hint: INDIEX vs. INDEX.


That's the index name you're looking at.
Re: Index problem [message #608628 is a reply to message #608626] Fri, 21 February 2014 07:56 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
Hi,

no it is not there, I also checked about the select * all_indexes.

Also, I am not faking at all i am just trying to hide main name but the problem is real!!

I am sorry i have misguided in any manners!!

script for index:

CREATE INDEX TABLE_POSITION_A_IDX2 ON TABLE_POSITION_ADJ
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

[Updated on: Fri, 21 February 2014 07:57]

Report message to a moderator

Re: Index problem [message #608629 is a reply to message #608627] Fri, 21 February 2014 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, only if it really was.
OP

SQL> drop index indiex_A_IDX2;
drop index index_A_IDX2
Re: Index problem [message #608630 is a reply to message #608628] Fri, 21 February 2014 08:05 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
It would really help if you would copy and paste your whole session, including the DROP and CREATE. If you need to obfuscate the object names, simply use Search & Replace. For example, search for "TABLE_POSITION_A_IDX2" and replace with "INDEX2". Understand?
Re: Index problem [message #608631 is a reply to message #608630] Fri, 21 February 2014 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's is no reason to suspect that the error message is incorrect, so check dba_objects like I already suggested
Re: Index problem [message #608633 is a reply to message #608630] Fri, 21 February 2014 08:14 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
This is what i get for both: logged in with the user

SQL> CREATE INDEX INDEX2 ON TABLE_INDEX2
2 (T_ID, P_ID, DELIVERY_TYPE, C_ID, START_DATE,
3 END_DATE)
4 LOGGING
5 TABLESPACE TBLSPC
6 PCTFREE 10
7 INITRANS 2
8 MAXTRANS 255
9 STORAGE (
10 INITIAL 64K
11 NEXT 1M
12 MINEXTENTS 1
13 MAXEXTENTS 2147483645
14 PCTINCREASE 0
15 FREELISTS 1
16 FREELIST GROUPS 1
17 BUFFER_POOL DEFAULT
18 )
19 NOPARALLEL;
CREATE INDEX INDEX2 ON TABLE_INDEX2
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop index INDEX2;
drop index INDEX2
*
ERROR at line 1:
ORA-01418: specified index does not exist
Re: Index problem [message #608634 is a reply to message #608633] Fri, 21 February 2014 08:16 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What does this give:

SQL> SELECT OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME='INDEX2';


Copy and paste the results.
Re: Index problem [message #608635 is a reply to message #608634] Fri, 21 February 2014 08:18 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
it is none
Re: Index problem [message #608636 is a reply to message #608635] Fri, 21 February 2014 08:30 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
I don't believe you, I'm afraid. Someone else will have to help you. I can't fly blind.
Re: Index problem [message #608637 is a reply to message #608636] Fri, 21 February 2014 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Oracle 11.2.0.1, OS Windows 2008 R2
not a supported combination
Re: Index problem [message #608640 is a reply to message #608637] Fri, 21 February 2014 09:37 Go to previous messageGo to next message
patdev
Messages: 73
Registered: August 2008
Member
Hi,

I have dropped the table and recreated but same error message comes up again.
ERROR at line 1:
ORA-00955: name is already used by an existing object
Re: Index problem [message #608641 is a reply to message #608640] Fri, 21 February 2014 09:39 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
submit a bug report to MOS

nobody here can change your reality.
Previous Topic: change db_cache_size
Next Topic: tables KEEP in KEEP POOL have lot of DML transactions
Goto Forum:
  


Current Time: Thu Mar 28 20:32:01 CDT 2024