Invisible Unused Indexes

WITH
    x
    AS
        (SELECT do.owner,
                t.name table_name,
                io.name index_name,
                DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring,
                DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used,
                ou.start_monitoring,
                ou.end_monitoring
           FROM sys.obj$          io,
                sys.obj$          t,
                sys.ind$          i,
                sys.object_usage  ou,
                dba_objects       do
          WHERE i.obj# = ou.obj#
            AND io.obj# = ou.obj#
            AND t.obj# = i.bo#
            AND i.obj# = do.object_id)
SELECT 'alter index ' || x.owner || '.' || x.index_name || ' invisible;'
  FROM x, dba_ind_columns um                           
 WHERE x.used = 'NO'
   AND x.owner = um.index_owner
   AND x.owner = um.table_owner
   AND x.index_name = um.index_name
   AND x.table_name = um.table_name
   AND (um.index_owner, um.table_name, um.column_name) NOT IN
        (SELECT f2.owner, f2.table_name, f2.column_name
           FROM dba_cons_columns f2);

 

 

Document:
https://blog.zeddba.com/2018/07/17/index-monitoring-in-oracle-database/