Find NOSEGMENT indexes that you forgot to delete

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.