Monitor Oracle Index Usage

CREATE OR REPLACE VIEW v$all_object_usage (owner,
                                           index_name,
                                           table_name,
                                           MONITORING,
                                           used,
                                           start_monitoring,
                                           end_monitoring
                                          )
AS
   SELECT u.NAME, io.NAME, t.NAME,
          DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
          DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
          ou.end_monitoring
     FROM SYS.obj$ io,
          SYS.obj$ t,
          SYS.ind$ i,
          SYS.object_usage ou,
          SYS.user$ u
    WHERE io.owner# = u.user#
      AND i.obj# = ou.obj#
      AND io.obj# = ou.obj#
      AND t.obj# = i.bo#;

GRANT SELECT ON SYS.v$all_object_usage TO "PUBLIC";

CREATE PUBLIC SYNONYM v$all_object_usage FOR SYS.v$all_object_usage;

 

SELECT 'ALTER INDEX ' || owner || '.' || index_name || '  MONITORING USAGE;'
  FROM dba_indexes
 WHERE owner IN ();

SELECT *
  FROM v$all_object_usage
 WHERE MONITORING = 'YES'
   AND used = 'NO'
   AND owner IN ();

SELECT 'ALTER INDEX ' || owner || '.' || index_name || 'NOMONITORING USAGE;'
  FROM dba_indexes
 WHERE owner IN ();