How to drop an index and create it as primary index with SQL command

[Former Member]

Dear Oracle Expert.

Background

I just upgraded a CRM from SP04 to SP05 with oracle 11g.

Problem.

I'm missing a primary index ,

the reason why it can' be created during the upgrade is , there exist an index /BI0/E0RSTT_C02~p of that table

1.          I need to delete index /BI0/E0RSTT_C02~p in the DB

2.          Create the primary index /BI0/E0RSTT_C02~0 with the same field of /BI0/E0RSTT_C02~p

To my knowledge, there is 2 way to do these

1. se11/se14

2. (st04) SQL commands

I'm more interested in the SQL commands.

Could you please provide me the whole scripts of doing the 2 points?

I tried but always get errors...

Thank you very much!

Kate

[Former Member · Accepted solution]

Hi Kate,

Please follow the below steps.

1.Tcode Se14.

2.      Choose the Indexes tab.

3.      To create an index, choose New/delete

4.      Enter a name for the index. Index names, like table names, also have a prefix, followed by an underscore.

If the name of an index was registered on the name server, it cannot be deleted.

5.      To select table fields, choose New.

6.      if the index is a unique index,

7.      If the index is used for all databases, choose  and whether it is to be created for all databases. Choose the appropriate checkboxes.

8.      Choose File→ Save All Metadata.

I would always suggest to use SAP to create index or deletion than in Sql commands.

Thanks and Regards,

Vimal

[Former Member · Accepted solution]

Hello Kori and Vimal,

I have to delete the ~p index first,

because the exist ~p index have the same fields as the primary index i want to create.

please check as below.

CREATE UNIQUE INDEX "/BI0/E0RSTT_C02~0" ON "/BI0/E0RSTT_C02"

("KEY_0RSTT_C02P",

"KEY_0RSTT_C02T",

"KEY_0RSTT_C02U",

"KEY_0RSTT_C021",

"KEY_0RSTT_C022",

"KEY_0RSTT_C023",

"KEY_0RSTT_C024",

"KEY_0RSTT_C025",

"KEY_0RSTT_C026",

"KEY_0RSTT_C027")

PCTFREE 10

INITRANS 002

TABLESPACE PSAPDAT

STORAGE (INITIAL 0000000016 K

NEXT 0000002560 K

MINEXTENTS 0000000001

MAXEXTENTS UNLIMITED

PCTINCREASE 0000

FREELISTS 004)

ORA-01408: such column list already indexed

DDL time(___1): ........21 milliseconds

The SQL statement was not executed

Index   could not be created completely in the database

Index /BI0/E0RSTT_C02-0 could not be created

Request for /BI0/E0RSTT_C02 could not be executed

BR,

Kate

[Former Member · Accepted solution]

Hello Vimal,

Could you provide me some online documents with detailed how to drop/create index with se14 , better with snapshot:)

Because  i've no idea with this Tcode.

Thank you very much!

Kate

[Former Member · Accepted solution]

Hi Kate,

Please try the following command and check whether it works.

drop index "/BI0/E0RSTT_C02~0";

CREATE UNIQUE INDEX "/BI0/E0RSTT_C02~0" ON "/BI0/E0RSTT_C02"

("KEY_0RSTT_C02P",

"KEY_0RSTT_C02T",

"KEY_0RSTT_C02U",

"KEY_0RSTT_C021",

"KEY_0RSTT_C022",

"KEY_0RSTT_C023",

"KEY_0RSTT_C024",

"KEY_0RSTT_C025",

"KEY_0RSTT_C026",

"KEY_0RSTT_C027")

TABLESPACE PSAPDAT

);

Thanks and Regards,

Vimal

[Former Member · Accepted solution]

If not, try to put ABAP schema before the index and check

Like

drop index SAPSR3. "/BI0/E0RSTT_C02~0";

[former_member188883 · Accepted solution]

Hi Kate,

As per the error message an index already exists with the fields you are using.

Please ensure no other index exists with the same fields.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Kate,

    For creation of index please used the below link.

http://saptechnical.com/Tutorials/ABAP/SecondaryIndex/Create.htm

http://saptechnical.com/Tutorials/ABAP/SecondaryIndex/Create.htm

For deletion, you can follow the below steps.

in the above link, where you are creating index, you can see the option to delete. Please use that

Thanks and Regards,

Vimal

[Former Member · Accepted solution]

Hello Vimal,

Thank you very much for the all your answer.

Really sorry for the delay because of some personally affaires.

Another question, can i drop the index using se11 or se14?

Best Regards,

Kate

[former_member188883 · Accepted solution]

Hi Kate,

Use SE14 to drop the index.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hello Deepak Kori,

So i click the button "delete database index" and the index will be droped both in ABAP DICT and oracle ?

Thank you!

Kate

[former_member188883 · Accepted solution]

Hi Kate,

SE14 will drop the index from the database . ABAP dictionary will hold only the field names and index name which u can delete it from SE11.

Regards,

Deepak Kori

[former_member188883]

Hi Kate,

Firstly you need to get the fields in the index using SE14 or Se11.

Later you may login to SQL prompt and use the SQL command below

CREATE UNIQUE INDEX <Index name> ON <Table_name>(Field1, field2...)

      TABLESPACE <Tablespace Name>

      STORAGE (INITIAL 20K

      NEXT 20k

      PCTINCREASE 75);

To drop the index use the following SQL

DROP INDEX <INDEX_NAME>;

Hope this helps.

Regards,

Deepak Kori