OraFAQ Forum: Server Administration » Index problem

Home » RDBMS Server » Server Administration » Index problem (Oracle 11.2.0.1, OS Windows 2008 R2)

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

Index problem [message #608621]

Fri, 21 February 2014 07:42

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!!

Report message to a moderator

Re: Index problem [message #608622 is a reply to message #608621]

Fri, 21 February 2014 07:44

gazzag
Messages: 1119
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.

Report message to a moderator

Re: Index problem [message #608623 is a reply to message #608622]

Fri, 21 February 2014 07:49

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>

Report message to a moderator

Re: Index problem [message #608624 is a reply to message #608623]

Fri, 21 February 2014 07:50

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

Report message to a moderator

Re: Index problem [message #608625 is a reply to message #608624]

Fri, 21 February 2014 07:53

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Index problem [message #608626 is a reply to message #608624]

Fri, 21 February 2014 07:53

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

Senior Member

select object_type, owner
from dba_objects 
where object_name = 'INDIEX_A_IDX2'

Report message to a moderator

Re: Index problem [message #608627 is a reply to message #608625]

Fri, 21 February 2014 07:54

cookiemonster
Messages: 13959
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.

Report message to a moderator

Re: Index problem [message #608628 is a reply to message #608626]

Fri, 21 February 2014 07:56

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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Index problem [message #608630 is a reply to message #608628]

Fri, 21 February 2014 08:05

gazzag
Messages: 1119
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?

Report message to a moderator

Re: Index problem [message #608631 is a reply to message #608630]

Fri, 21 February 2014 08:10

cookiemonster
Messages: 13959
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

Report message to a moderator

Re: Index problem [message #608633 is a reply to message #608630]

Fri, 21 February 2014 08:14

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

Report message to a moderator

Re: Index problem [message #608634 is a reply to message #608633]

Fri, 21 February 2014 08:16

gazzag
Messages: 1119
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.

Report message to a moderator

Re: Index problem [message #608635 is a reply to message #608634]

Fri, 21 February 2014 08:18

patdev
Messages: 73
Registered: August 2008

Member

it is none

Report message to a moderator

Re: Index problem [message #608636 is a reply to message #608635]

Fri, 21 February 2014 08:30

gazzag
Messages: 1119
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.

Report message to a moderator

Re: Index problem [message #608637 is a reply to message #608636]

Fri, 21 February 2014 08:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Oracle 11.2.0.1, OS Windows 2008 R2
not a supported combination

Report message to a moderator

Re: Index problem [message #608640 is a reply to message #608637]

Fri, 21 February 2014 09:37

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

Report message to a moderator

Re: Index problem [message #608641 is a reply to message #608640]

Fri, 21 February 2014 09:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

submit a bug report to MOS

nobody here can change your reality.

Report message to a moderator

Previous Topic:	change db_cache_size
Next Topic:	tables KEEP in KEEP POOL have lot of DML transactions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 21:57:19 CST 2024