ORA-01408 and ORA-01418

As I was applying a software release recently, I saw the two error messages above and have recreated them in the example below:

SQL> create table table1 (column1 number)

  2  /

 

Table created.

SQL> create index indexl on table1(column1)

  2  /

 

Index created.

SQL>

TABLE1 has an index called INDEXL but the SQL below tries to drop an index called INDEX1, which does not exist. This produces an ORA-01418:

SQL> drop index index1

  2  /

drop index index1

           *

ERROR at line 1:

ORA-01418: specified index does not exist

SQL>

The SQL below tries to add an index to COLUMN1. As the DROP INDEX statement above failed, TABLE1 still has an index on COLUMN1. This causes the SQL to fail with an ORA-01408:

 

SQL> create index index1 on table1(column1)

  2  /

create index index1 on table1(column1)

                              *

ERROR at line 1:

ORA-01408: such column list already indexed

 

SQL>