cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot drop index

Former Member
0 Kudos

Dear All,

during EHP2 installation (CRM) we faced a problem on MAIN_SHDIMP/SUBMOD_SHDIMP/SHADOW_IMPORT_INC phase:

{code}

2EETW000 Update and insert sequence failed due to conflicting unique indexes on table COMC_ATTRIBUTE

{code}

We have tried everything including changing index in se11, then activating in se14, but still nothing. As far as we know the last solution is to drop existing index on db level and create new one but non-unique. Sounds easy, but...

{code}

SQL> select index_name, owner, visibility from dba_indexes where table_name='COMC_ATTRIBUTE' and tablespace_name='PSAPSR4';

INDEX_NAME                     OWNER                          VISIBILIT

------------------------------ ------------------------------ ---------

COMC_ATTRIBUTE~UNI             SAPSR4                         VISIBLE

COMC_ATTRIBUTE~0               SAPSR4                         VISIBLE

SQL> drop index sapsr4.COM_ATTRIBUTE~UNI;

ORA-00911: invalid character

SQL> drop index sapsr4."COM_ATTRIBUTE~UNI";

ORA-01418: specified index does not exist

{code}

We've tried tens of options like single and double quotes, but still nothing. Please advice how to drop this index..

Thanks in advance!

Accepted Solutions (0)

Answers (3)

Answers (3)

Reagan
Advisor
Advisor
0 Kudos

INDEX_NAME                     OWNER                          VISIBILIT

------------------------------ ------------------------------ ---------

COMC_ATTRIBUTE~UNI             SAPSR4                         VISIBLE

COMC_ATTRIBUTE~0               SAPSR4                         VISIBLE

SQL> drop index sapsr4.COM_ATTRIBUTE~UNI;

ORA-00911: invalid character

SQL> drop index sapsr4."COM_ATTRIBUTE~UNI";

ORA-01418: specified index does not exist

A small typo.

/RB/

volker_borowski2
Active Contributor
0 Kudos

Hi,

double quotes work with "~"-char, used it a lot.

No idea how this can occur, Stefans idea of char corruptions in the name seems likely.

You could try in addition

select index_name, length(index_name) ...

to check out if there are invisible chars.

What does a SE14 -> CHECK DB OBJECT for the table says about the index ?

Notes 601757 + 1003992 (very old) know the problem in an earlier upgrades.

May be some postprocessing did not take place in your system then?

There is a Reference to a correction report.

Unfortunately the solution in these notes is to drop the index

Volker

volker_borowski2
Active Contributor
0 Kudos

ARG !!!!

selecting COMC_

and trying to drop COM_

...

Just did not see it myself on first read !

Volker

stefan_koehler
Active Contributor
0 Kudos

Hi Pawel,

it seems like that the index name "COMC_ATTRIBUTE~UNI" includes some special characters. I wonder how this can happen, if all indexes were created by SAP. It is quite common, if the indexes were created by "third party" tools (like SQL*Plus with wrong keyboard settings and backspace, etc.).

However .. the following procedure should only be used, if all other supported ways (like DROP INDEX, etc.) failed - but it works

SYS@T11:17> select index_name, owner, visibility from dba_indexes where table_name='COMC_ATTRIBUTE';

INDEX_NAME                           OWNER                                    VISIBILIT

------------------------------ ------------------------------ ---------

COMC_ATTRIBUTE~UNI                 SYS                                    VISIBLE

SYS@T11:17> select OBJ#, DATAOBJ#, NAME from obj$ where NAME like '%COMC_ATTRIBUTE%';

      OBJ#   DATAOBJ# NAME

---------- ---------- ------------------------------

     83122          83122 COMC_ATTRIBUTE

     83123          83123 COMC_ATTRIBUTE~UNI

SYS@T11:17> update obj$ SET NAME = 'ZDROPINDEX' where OBJ# = 83123 and DATAOBJ# = 83123;

SYS@T11:17> commit;

SYS@T11:17> select index_name, owner, visibility from dba_indexes where table_name='COMC_ATTRIBUTE';

INDEX_NAME                           OWNER                                    VISIBILIT

------------------------------ ------------------------------ ---------

ZDROPINDEX                           SYS                                    VISIBLE

SYS@T11:17> drop index ZDROPINDEX;

Regards

Stefan