How to to find unused indexes and delete tham to release space

PURPOSE

To show how to view all indexes being monitored.

SCOPE & APPLICATION

Instructional.

Viewing All Indexes Being Monitored Under Another User's Schema:
=================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose usage is being monitored.

'ALTER INDEX MONITORING USAGE'

places an entry in V$OBJECT_USAGE for that particular index to help determine if the index is being used or not. The V$OBJECT_USAGE view uses the username logged into database when the 'ALTER INDEX MONITORING USAGE' is issued. This will not enable any user otherthan the user who issued the 'ALTER INDEX MONITORING USAGE' to view if index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#

SQL> select index_name, table_name, uniqueness, status from user_indexes where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUSPK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORINGPK_EMP EMP YES NO 10/12/2001 06:42:35

Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;
Connected.

SQL> select * from v$object_usage;
no rows selected

To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,INDEX_NAME, TABLE_NAME, MONITORING,
USED, START_MONITORING, END_MONITORING)
as select u.name, io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring from sys.user$ u, 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#and u.user# = io.owner#

View created.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORINGSCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35