Enabling Index Monitoring to find if the indexes are being used:


::::::::::::::
1.sql
::::::::::::::
select table_name, index_name, MONITORING, USED from v$object_usage where index_name in
(
'PUSER_IDX_3',
'PUSER_IDX_4',
'PUSER_IDX_6',
'PUSER_IDX_3',
'PUSER_IDX_4',
'USER_IDX'
)
/
::::::::::::::
2.sql
::::::::::::::
-- note not altering the session as i am identifying the index with schema name

--spool enable_monitoring.log
alter index  RAJ.PUSER_IDX_3 monitoring usage;
alter index  RAJ.PUSER_IDX_4 monitoring usage;
alter index  RAJ.PUSER_IDX_6 monitoring usage;
alter index  RAJ.PUSER_IDX_3 monitoring usage;
alter index  RAJ.PUSER_IDX_4 monitoring usage;
alter index  ORACLE.USER_IDX monitoring usage;
::::::::::::::
3.sql
::::::::::::::
select table_name, index_name, MONITORING, USED from v$object_usage where index_name in
(
'PUSER_IDX_3',
'PUSER_IDX_4',
'PUSER_IDX_6',
'PUSER_IDX_3',
'PUSER_IDX_4',
'USER_IDX'
)
/


-- Sql to check the index monitoring

  select du.username, io.name index_name, t.name table_name,
         decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitor,
         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_users du
  where io.owner# = du.USER_ID
    and i.obj# = ou.obj#
    and io.obj# = ou.obj#
   and t.obj# = i.bo#
   and io.name in (
 'PUSER_IDX_3',
 'PUSER_IDX_4',
 'PUSER_IDX_6',
 'PUSER_IDX_3',
 'PUSER_IDX_4',
 'USER_IDX'
 )
/


USERNAME   INDEX_NAME                     TABLE_NAME                     MONI USED START_MONITORING    END_MONITORING
---------- ------------------------------ ------------------------------ ---- ---- ------------------- -------------------
RAJ PUSER_IDX_3       USER_INFO             YES  NO   02/21/2013 16:09:28
RAJ PUSER_IDX_4       USER_INFO             YES  NO   02/21/2013 16:09:28
RAJ PUSER_IDX_3     P_USER_INFO          YES  NO   02/21/2013 16:47:55
RAJ PUSER_IDX_4   P_USER_INFO          YES  NO   02/21/2013 16:10:52
RAJ PUSER_IDX_6   P_USER_INFO          YES  NO   02/21/2013 16:09:28
ORACLE     USER_IDX     USER_INFO             YES  NO   02/21/2013 16:09:28

6 rows selected.