Err is human and I, as human, I do forget sometimes to clean-up some tests.
Recently this happen with NOSEGMENT indexes. Yesterday I received an email from a colleague, reporting some errors he received with Datapump:
ORA-08114: can not alter a fake index Failing sql is: ALTER INDEX "SUPERAPP"."IDX_POSITION_TEST" NOPARALLEL ORA-39083: Object type INDEX failed to create with error: ORA-08114: can not alter a fake index
The indexes created with NOSEGMENT do not appear in DBA_INDEXES (don’t get fool by the SEGMENT_CREATED column).
They do appear on DBA_OBJECTS, but there they look like any other index.
The solution is to match the two tables. So to find the NOSEGMENT indexes that you forgot to delete you can use the following query:
SELECT 'DROP INDEX '||owner||'.'||object_name||';' cmd, created FROM dba_objects WHERE object_type='INDEX' AND object_name NOT IN (SELECT index_name FROM dba_indexes);
If you wonder what are NOSEGMENT indexes, a good source is Oracle-Base.