Find indexes that are useless or not of the proper type

There is a rule of thumbs according to which a normal non-unique index should be quite selective to be taken by the CBO. Selective means rather to return only rather less than 5 % than 10 % of the data.
On the other hand, bitmaps are good on columns that are not selective at all but you have several of those on one table and queries restrict on a variety of combinations on bitmap indexed columns which in combination are selective. The more selective a bitmap indexed column is the more bloated gets the index.
Following query can help to find indexes that should be re-considered.

with IND_BASE
     as (select *
           from ALL_INDEXES
          where UNIQUENESS = 'NONUNIQUE' -- UNIQUE indexes are per definition
                                         -- very selective
            and OWNER not in ('SYS'
                            , 'OPS$DEZA'
                            , 'SYSTEM'
                            , 'XDB'))
   , COL_BASE
     as (select C.*     --  density = 1 / (Number of distinct NON null values)
           from ALL_TAB_COLS C
                inner join IND_BASE I
                   on C.OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME)
   , COLS_INVALID_DENS
     as (select *
           from COL_BASE
          /* we must rule out indexes with columns without or with invalid
             density information */
          where DENSITY is not null
             /* 0 or negative density is actually not defined but at least 0s
                occur for some obscure reason */
             or DENSITY > 0)
   , IND_COL_BASE
     as (select C.*
           from ALL_IND_COLUMNS C
                inner join IND_BASE I
                   on C.TABLE_OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME
                  and C.INDEX_OWNER = I.OWNER
                  and C.INDEX_NAME = I.INDEX_NAME)
   , IND_INVALID_DENS
     as (  select I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME
             from IND_COL_BASE I
                  inner join COLS_INVALID_DENS C
                     on I.TABLE_OWNER = C.OWNER
                    and I.TABLE_NAME = C.TABLE_NAME
                    and I.COLUMN_NAME = C.COLUMN_NAME
         group by I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME)
   , IND_VALID_BASE
     as (select I.*
           from IND_BASE I
                left outer join IND_INVALID_DENS IV
                   on I.TABLE_OWNER = IV.TABLE_OWNER
                  and I.TABLE_NAME = IV.TABLE_NAME
                  and I.OWNER = IV.INDEX_OWNER
                  and I.INDEX_NAME = IV.INDEX_NAME
          where IV.TABLE_OWNER is null)
   , IND_VALID_COL
     as (select C.*, I.INDEX_TYPE
           from IND_VALID_BASE I
                inner join IND_COL_BASE C
                   on I.TABLE_OWNER = C.TABLE_OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.OWNER = C.INDEX_OWNER
                  and I.INDEX_NAME = C.INDEX_NAME)
   , IND_COL_DENS
     as (select I.*, C.DENSITY as DENS
           from IND_VALID_COL I
                inner join COL_BASE C
                   on I.TABLE_OWNER = C.OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.COLUMN_NAME = C.COLUMN_NAME)
   , IND_DENS
     as (  select INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE
                , exp(sum(ln(DENS))) as DENS -- instead of multiplying numbers,
                                             -- their logarithms can be added, 
                                             -- and the result exponentiated: 
                                             -- only works if the density > 0
             from IND_COL_DENS
         group by INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE)
select /*+ parallel(4) */
      'Please consider converting the index into an normal index'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'BITMAP'
   and DENS <= 0.1
union all
select /*+ parallel(4) */
      'Please consider removing the index or converting it into an bitmap index, if it is a compound index into several bitmap indexes'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'NORMAL'
   and DENS >= 0.1;