@@header

set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display index usage monitoring
*  Parameters : NONE
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Mar-12  Vishal Gupta  Intial version
*
*
*/
set term on

COLUMN table_owner          HEADING "Table Owner"            FORMAT a20
COLUMN table_name           HEADING "Table Name"             FORMAT a20
COLUMN index_name           HEADING "Index Name"             FORMAT a20
COLUMN Monitoring           HEADING "Monitoring"             FORMAT a10
COLUMN Used                 HEADING "Used"                   FORMAT a4


select 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
where i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
;
  

@@footer