CREATE TABLE scott.index_usage_count(
table_owner  VARCHAR2(30),
table_name   VARCHAR2(30),
index_owner  VARCHAR2(30),
index_name   VARCHAR2(30),
count_usage  NUMBER,
last_check	     DATE);

ALTER TABLE scott.index_usage_count 
ADD PRIMARY KEY(index_owner,index_name);

REM Die folgenden Befehle als SYS ausführen ########################################

REM Wir legen eine View an, die alle überwachten Indizes aus allen Schemata anzeigt.

CREATE OR REPLACE VIEW sys.my_index_usage
AS
SELECT u2.name as table_owner, t.name as table_name,
u1.name as index_owner,io.name as index_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as MONITORING,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES')as USED,
ou.start_monitoring, ou.end_monitoring   
FROM   sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u1, sys.user$ u2
WHERE  i.obj# = ou.obj#
AND  io.obj# = ou.obj#
AND t.obj# = i.bo#
AND u1.user#= io.owner#
AND u2.user#= t.owner#; 

REM Wir vergeben das Leserecht an der View dem gewünschten Benutzer.
GRANT SELECT on sys.my_index_usage TO scott;

REM Da der Benutzer die Indexüberwachung ein- und ausschaltet, braucht er für fremde
REM Indizes das Recht ALTER ANY INDEX
GRANT ALTER ANY INDEX TO scott;

REM Ab hier kann wieder mit dem normalen Benutzer-Account geabeitet werden ########

REM Procedure zum Heraufzählen der Indexbenutzung

CREATE OR REPLACE PROCEDURE  scott.check_idx_usage
IS
TYPE my_type IS TABLE OF sys.my_index_usage%ROWTYPE;
my_t my_type;

BEGIN

SELECT * BULK COLLECT INTO my_t FROM sys.my_index_usage;
FOR i IN 1 .. my_t.count LOOP
	IF my_t(i).used='YES' THEN
	    BEGIN
		UPDATE scott.index_usage_count  
	       SET count_usage =  count_usage+1, last_check=sysdate
	     WHERE index_owner=my_t(i).index_owner 
		   AND index_name= my_t(i).index_name;
		IF SQL%ROWCOUNT=0 THEN -- Zeile ist noch nicht vorhanden, also eintragen
			INSERT INTO scott.index_usage_count
			(index_owner, Index_name,table_owner,table_name,count_usage,last_check) 
			VALUES (
			my_t(i).index_owner,my_t(i).index_name,
			my_t(i).table_owner,my_t(i).table_name,
			1,sysdate); 
		END IF;
	    EXCEPTION WHEN OTHERS THEN
	         RAISE;
	    END;
		EXECUTE IMMEDIATE 'alter index '||my_t(i).index_owner||'.'||my_t(i).index_name||' nomonitoring usage';
		EXECUTE IMMEDIATE 'alter index '||my_t(i).index_owner||'.'||my_t(i).index_name||' monitoring usage';
	END IF;
END LOOP;
END;
/
show errors


REM Indexüberwachung einschalten (das können Sie nun für alle interessanten Indizes durchführen):
ALTER INDEX scott.pk_emp MONITORING USAGE;

REM Nun testen wir das Ganze ...

REM Überwachten Index benutzen
SELECT * FROM scott.emp WHERE empno=7900;

REM Procedure starten
exec scott.check_idx_usage

REM Die Ausgabe könnte dann so aussehen:
select * from scott.index_usage_count;

TABLE_OWNER   TABLE_NAME    INDEX_OWNER    INDEX_NAME      COUNT_USAGE    LAST_CHECK                
-----------  -----------  --------------  -------------  -------------  -------------- 
SCOTT         BIG            SCOTT         BIG__IDX               1         27.05.08                  
SCOTT         EMP            SCOTT         PK_EMP                 3         28.05.08

REM Das Ganze kann man jetzt noch in einen Job packen und z.B. einmal pro Viertelstunde laufen lassen
DECLARE
   ret NUMBER;
BEGIN
   DBMS_JOB.SUBMIT(ret, 'scott.check_idx_usage;',
   sysdate,'sysdate+1/24/4'); 
   COMMIT;
END;
/