ora-01408 such column list already indexed

[Antonio]

hi erman,

we are facing an error,while we trying to create index.
which is "ora-01408 such column list already indexed"

But there isnt any index like that.When i search indepth,ı found that in index in recyclebin.
After that i tried to purge this and purge was succesful.But when i check recycle bin,i can still see this index in recyclebin

[ErmanArslansOracleBlog]

You have an index on that column.
you may not see it because the name of the index may be weird.. The index name may be starting with "BIN$"
This happens when you restore a table from recyclebin. Table name is restored as the original name, but trigger names and index names are restored with their recyclebin names..
Check dba_ind_columns/all_ind_columns, you will see the index when you query these views.
Drop that index using drop index "OWNER"."<index_name>" . For ex: drop index "PO"."BIN$DRERERERRE"
Then you will be able to create your new index on the relevant column.

Alternatively you can rename that index(name starting with BIN$) to "index name you want".

[Antonio]

I did that and ty erman problem solved :)

[Antonio]

I followed the steps as you said;

select * from all_ind_columns where table_name like '%PO_HEADERS_ALL%';

drop index "PO"."BIN$QyQx67tybh83qA==$0"