set lines 110 pages 500

col tbln format a30 heading 'Table'
col indx format a30 heading 'Index'
col ityp format a3 heading 'Typ'

variable usn varchar2(20);
exec :usn := '&USER';

break on tblo on tbln

select table_name tbln
     , index_name indx
     , decode(INDEX_TYPE, 'NORMAL','NML','BITMAP','BMP','IOT - TOP','IOT','DOMAIN','DOM','LOB','LOB'
                        , 'FUNCTION-BASED BITMAP','FBB','FUNCTION-BASED NORMAL','FBN'
                        , INDEX_TYPE) ityp
 from all_indexes
where owner=:usn
  and table_owner=:usn
order by table_name
      , index_name
/

spool &&USER._MON_INDEX_ON.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
 from all_indexes
where owner=:usn
  and table_owner=:usn
order by table_name, index_name
/

spool off;


col tbln format a30 hea 'Table'
col indx format a30 hea 'Index'
col mntr format a10 hea 'Monitoring'
col used format a4 hea 'Used'

break on tbln

select  table_name tbln
      , index_name indx
      , monitoring mntr
      , used
from  v$object_usage
order by table_name
      , index_name
/