-- E. Nossova, Product TuTool : www.tutool.de


/* this script turns on and off index usage
monitoring, additionally it reports information
about index usage for one owner,
input parameters:
        index_owner - index owner, 
                      default: current user for
                      monitoring, all owners for
                      viewing,
        table_owner - table owner,
                      default: all owners,
        table_name - table name, 
                      default: all tables,
        index_name - index name,
                      default: all indexes,
        action - on - turn on index monitoring,
                 off - turn off index monitoring,
                 view - report information about
                        index usage,
                        default: view */

define index_owner='&index_owner'
define table_owner='&table_owner'
define table_name='&table_name'
define index_name='&index_name'
define action='&action'

set verify off
set feedback off
set pagesize 1000
set linesize 1000

col index_owner format a30
col index_name format a30
col table_name format a30
col start_monitoring format a20
col end_monitoring format a20
col used format a4
col monitoring format a10

declare
cursor cur_ind is
select owner, index_name from sys.dba_indexes
where
owner = nvl('&index_owner',user) and
owner not in ('SYS','SYSTEM') and
table_owner = nvl('&table_owner',table_owner) and
table_name = nvl('&table_name',table_name) and
index_name = nvl('&index_name',index_name);
begin
	if upper('&action') = 'ON' then
		for rec_cur_ind in cur_ind loop
			begin
				execute immediate 'alter index '|| rec_cur_ind.owner || '.' || rec_cur_ind.index_name ||' monitoring usage';
			exception when others then
				null;
			end;
		end loop;
	else
		if upper('&action') = 'OFF' then
			for rec_cur_ind in cur_ind loop
				begin
					execute immediate 'alter index '|| rec_cur_ind.owner || '.' || rec_cur_ind.index_name ||' nomonitoring usage';
				exception when others then
					null;
				end;
			end loop;
		end if;
	end if;
end;
/

select u.name index_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.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where nvl(upper('&action'),'VIEW') in ('VIEW', 'ON', 'OFF')
  and u.name = nvl('&index_owner',u.name)
  and io.owner# = u.user#
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
  and io.name = nvl('&index_name', io.name)
  and t.name = nvl('&table_name', t.name)
order by u.name, t.name, io.name
/



undefine index_owner
undefine table_owner
undefine table_name
undefine index_name
undefine action