Monthly Archives: December 2018

Index columns query

Query to show all indexes, its characteristics and respective columns from a table.
Note: It does not filter on owner.

select c.index_name,
       c.table_name||'('||listagg(column_name,',') within group (order by column_position)||')' as columns,
       decode(status,'VALID',null,status||' ')
       ||decode(visibility,'VISIBLE',null,visibility||' ')
       ||decode(segment_created,'YES',null,'EMPTY ')
       ||decode(uniqueness,'NONUNIQUE',null,uniqueness||' ')
       ||funcidx_status as STATUS
 from dba_ind_columns c, dba_indexes i
where i.table_name=c.table_name and i.index_name=c.index_name
  and c.table_name='&TABLE_NAME'
group by c.table_name,c.index_name,i.status,visibility, segment_created,uniqueness,funcidx_status
order by c.table_name,c.index_name;

Example of output:

INDEX_NAME                     COLUMNS                                 STATUS  
------------------------------ --------------------------------------- ----------
LIB_VERSION_NAME_IDX           LIB_VERSION(VERSION_NAME)                     
LIB_VERSION_PK                 LIB_VERSION(ID)                         UNIQUE

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:
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.